topic Set analysis in QlikView

Set analysis

Fri, 06 Jul 2012 03:29:24 GMT

Hello,

this is my data ,, by default i need the value in textbox to be for last year,last quarter and last month ,,

i tried with the following code =sum({<Year={$(=max(Year))},Qtr={$(=max(Qtr))},Month={$(=max(Month))}>}StkAmt)

but it shows value as 0 instead of 4 ,,, but when i select something then i get right value ,, can someone help me with script and explain why my script didnt work

Re: Set analysis

Fri, 06 Jul 2012 03:31:01 GMT

Year	Month	Sum (StkAmt)
		86
1	1	6
1	2	7
1	3	5
1	4	4
1	5	7
1	6	8
1	7	3
1	8	2
1	9	7
1	10	5
1	11	8
1	12	2
2	1	6
2	2	7
2	3	5
2	4	4

Hello,

this is my data ,, by default i need the value in textbox to be for last year,last quarter and last month ,,

i tried with the following code =sum({<Year={$(=max(Year))},Qtr={$(=max(Qtr))},Month={$(=max(Month))}>}StkAmt)

but it shows value as 0 instead of 4 ,,, but when i select something then i get right value ,, can someone help me with script and explain why my script didnt work

Re: Set analysis

Oleg_Troyansky — Fri, 06 Jul 2012 04:02:05 GMT

Check if your formula works without the Month condition. If it does, than I believe the problem is in the different Data type. If your Month is formatted as a dual field, then comparing it to max(Month) won't work, because the result of the function max() is a number.

I'd recommend defining a YearMonth field as the MonthStart() date and comparing it to the date(max(YearMonth)) - this way, you replace multiple conditions with a single one, and it works properly.

I'm working on a Set Analysis training class, where I will be covering this and many other tricks related to Set Analysis - stay tuned.

Re: Set analysis

Anonymous — Fri, 06 Jul 2012 04:12:23 GMT

A bit tricky and ugly set analysis but this should pick the Max Year and the Max Month available for that year and sum up the StkAmt for that combo:

=sum({$<Year={$(=Max(Year))}, Month={$(=Max({$<Year={$(=Max(Year))}>} Month))}>} StkAmt)

Re: Set analysis

Fri, 06 Jul 2012 06:08:39 GMT

yes that worked ... thanks