IF ELSE Statement

[Manu0521]

Hi,

I have to show Account Active or DeActive based on the last 6 month Sales
   Sales                            Decision Column based on last 6 months

Account 
103                                     Active

105                                   DeActive

What should my expression be for the Decision column

I am using below expression, but it doesnt give me any result just the column name is displayed.
if(tuple(lastperiod(6,#CurrentMonth MUN Member#),Sales)>0)
Then
('Active')
Else
('DeActive')
                     
can last period be used as one parameter of tuple.

Thanks,
Manu

[Francis aka khayman]

i tried your expression it does not work. error message is like ... invalid parameter, using 'memberset' type but is expected to be a member yadah yadah yadah....

do you use that expression used in a crosstab or list?

anyway, in a list, i used
[data item1] = total(currentMeasure within set lastPeriods(6;<MUN of month>))

[data item2] = if ([data item1] > 0) then ('Active') else ('DeActive')

and it works

[Manu0521]

Hi Khayman,
Thanks for the reply. I am using it in a cross tab  with Accounts in Row and Months on column1 and Decision column on column 2.
My Expression is

I created a data item1 and
Data Item 1- total(currentMeasure within set lastPeriods (6,#'[Sales].[Time Period].[Time Period].[Months]->:[PC].[@MEMBER].[' + timestampMask(_first_of_month($current_timestamp),'yyyymmdd')+ '-' + timestampMask(_last_of_month($current_timestamp),'yyyymmdd') + ']'#))

This will give me the last 6 months from current month. Validated shows no error.

Decision column - if([Data Item 2] >0 ) then ('Active) else ('DeActive')

No error, but decison column displays "--" as a result,

Also my question is will this calculation consider with respect to accounts in the rows as they are not a part of the expression?

Thanks,
Manu

[Manu0521]

if([Data Item1]>0)  THEN (1)
ELSE (0)

This works and the same expression

if([Data Item1]>0)  THEN ('Active')
ELSE ('Not Active')

Gives just dashes in the result.

How to achieve this. Something to do with string and number.

Thanks,
Manu

[johnGQ]

to display text on cells, you may insert a conditional block to the cell; or upgrade to the latest version that claims to be to display text on the cell

[Manu0521]

Hi,
I am in cognos 10.2.1
Is there any other way to achieve this, why does this not work
if([Data Item1]>0)  THEN ('Active')
ELSE ('Not Active')
If using conditional block ,where should I set the text.

Thanks,
Manu

[johnGQ]

Place that computed text as item into the conditional block.

[Manu0521]

 I am still not getting this.

I create two static data item with Text ' Active' and 'InActive'
and used it on the decision column as

IF([Data Item1]>0)
THEN
([ACTIVE])
ELSE
([INACTIVE])

Expression for DataItem1 -[data item1] = "total(currentMeasure within set lastPeriods(6;<MUN of month>))"

but still the decision column displays dashes.
And if I use case statement then it will throw me an error.

Please advise

Thanks,
Manu

[johnGQ]

try to add a block variable based on your if ...else logic, so place each of the text items to a current block of the conditional block.

to test it can display text on cell, just hard-code your [data item 1] to be a value such as 2 (don't use your formula), then place your two static items per the block variable value 1 or 0, into each of the "current block" of the conditional block, you should see the text on cell; then you think how to plug your formula in.   

[Manu0521]

I am not able to place any item on the block.

Steps I followed.

1)Added a decision column with expression
   if([DataItem1]>0)
   Then(1)
   Else(0)
   DataItem1 expression is total(currentMeasure within set lastPeriods (6,MUN)).

2)Now all I need is to display 1 as Active and 0 as InActive on the decision column.

3) Created 2 separate static text data items as ActiveTxt and InActiveTxt with text 'Active' and 'InActive'.

4) Now added a conditional block variable under the report and it asks for a variable type . Now what expression should this variable contain and what variable should I use.
Should I use a string variable and add 2 values say 0 and 1 , what expression should string variable have. How do I link this with my decision column.

Please let me know the suggestions.

Thanks,
Manu

[johnGQ]

sincerely urge to put crosstab aside, just try out how to work out a conditional block first, then go back to your crosstab.

[Manu0521]

Hi John,

I went through creating conditional blocks.

I have a cross tab with a data item in row and two columns
QTY      Product   Code
A            15           1
B            20            0
C             25            1

Now in the code column I just need to replace 1 with a text and 0 with another text.

I am not able to place the  conditional block only on the code column, it places over all the fact cells meaning both product and code,.

I created a block variable - basically a boolean with expression [Query].

Code Select Expand

=1
Here two values will be one as yes and No
If its Yes then I will drop a Text item to the conditional block which has a text item active
If its No then I will drop a Text item to the conditional block which has a text item Inactive

The issue here is I am unable to place the block variable only  over the code column alone.

Thanks,
Manu

[Francis aka khayman]

this is the same issue as this:

http://www.cognoise.com/index.php/topic,26958.0.html

right?