Set expression - question

[#1]

Hi,

I have a problem with a reporting- requirement that keeps coming up in different projects, so I thought maybe one of you would have a great idea, how to solve it...

The problem is I need to dynamically compare to monthly values in a table, that also contains the dimension month (see screenshot - 3rd column).

I can't use the above() - function because the previous month is not always displayed in the table (i.e. there is no October in the sample graph)

I could get it to work for with hard - coded month with this formula (2nd expression):  sum({$<Month={1}>} Total Value)

It didn't work when I tried to reference the month of the current row with something like this (3rd expression): sum({$<Month={"$(=only(Month)-1)"}>} Total Value)

Please find attached a qvw that illustrates the problem. I hope someone can give me a hint, how to handle this.

Thanks in Advance!

Cheers,

Gerald

[#2]

hi,

try to help..might not be ur solution but i'm tryin to understand what you want to achieved for the end result.

[Sokkorn]

Hi Gerald,

Try this set analysis =sum({$<Month={"$(=max(Month)-1)"}>} Total Value)

Regards,

Sokkorn Cheav

[rohit214]

hi

please find the attacment

[luciancotea]

You can consider 2 other ways that are not using SET:

1. In LOAD script create another column which contains the precedent month value.

2. Create a secondary disconected TIME table and use:

   " =SUM( IF( Date = Dis_Date, Value ) )" for current month

   " =SUM( IF( Date = addmonth(Dis_Date,-1), Value ) )" for last month

[#6]

Hi,

thanks for all the good ideas

But I haven't found the solution I could use yet:

1) I can't use above() because it is only possible when the previous column is also visible in the table  (which is not the case for the month 11)

2) The approach using the max() - function works if I only have two months, but if I want to display 3 or more months it won't work anymore.

3) I can't us the Load - Skript because the analysis is quite dynamic and I can't forsee all selections.

So is there any way that I could use a set that references a dimension value - the month - of the current row?

[luciancotea]

Did you tried my second solution?

2. Create a secondary disconected TIME table and use:

   " =SUM( IF( Date = Dis_Date, Value ) )" for current month

   " =SUM( IF( Date = addmonth(Dis_Date,-1), Value ) )" for last month

[SunilChauhan]

in your third expression use code below

above(sum(Value))

[#9]

Hi,

yes that works indeed.

But unfortunately there is to much data and to little Ram in the real customer - qvw, where I need to use this .... ;-(.

Cheers,

Gerald

[luciancotea]

Ram is cheap. Offering a solution to a customer is priceless!