COGNOiSe.com - The IBM Cognos Community

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

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

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

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

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

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

Place that computed text as item into the conditional block.

 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

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.   

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

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

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].=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

this is the same issue as this:

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

right?