topic Set expression - question in QlikView

Set expression - question

Wed, 14 Dec 2011 14:05:59 GMT

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

Re: Set expression - question

Thu, 15 Dec 2011 00:30:26 GMT

hi,

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

Re: Set expression - question

Sokkorn — Thu, 15 Dec 2011 01:10:53 GMT

Hi Gerald,

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

Regards,

Sokkorn Cheav

Re: Set expression - question

rohit214 — Thu, 15 Dec 2011 05:30:27 GMT

please find the attacment

Re: Set expression - question

luciancotea — Thu, 15 Dec 2011 07:38:28 GMT

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

Set expression - question

Thu, 15 Dec 2011 12:52:23 GMT

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?

Set expression - question

luciancotea — Thu, 15 Dec 2011 12:56:01 GMT

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

Set expression - question

SunilChauhan — Thu, 15 Dec 2011 14:39:45 GMT

in your third expression use code below

above(sum(Value))

Set expression - question

Thu, 15 Dec 2011 14:46:01 GMT

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

Set expression - question

luciancotea — Thu, 15 Dec 2011 16:15:12 GMT

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