topic Help with set analysis in QlikView

Help with set analysis

amit_shetty78 — Fri, 18 Sep 2009 17:22:13 GMT

Hi all,

I need some help with expressions in a pivot chart. I think it may be possible to get the results using set analysis but I am not sure how.

The problem is thatI need to calculate values for a dimension but the data is not in the current selection resultset but outside it. The scenario and the required output is shown in the attached.

Appreciate any help on this.

Thanks.

Help with set analysis

Fri, 18 Sep 2009 18:14:55 GMT

Don't know whether i got your requirements correctly. Just check it,

Stat1: sum({1<Month={'xxx'},Type={'W'},tFlag={'1'}>} EOYCount)

Stat2: sum({1<tFlag={'1'}>} tFlag)

Help with set analysis

amit_shetty78 — Fri, 18 Sep 2009 18:55:19 GMT

Thanks for the reply Richy. I tried the same earlier and I am getting stat1 values as 0 as shown:

Range	Jul-09				YTD
Form	V#	IV#	Total	Stat 1	V#	IV#	Total	Stat 1	V#	IV#	Total	Stat 1
T	78	102	180	0	494	1087	1581	0	0	0	0	24733
O	199	133	332	0	1242	2082	3324	0	0	0	0	32966

This seem because wherever the value of month is eoy, then value in dt is blank and hence does not have corresponding range.

I want the result to be say when range 'Jul-09' and 'YTD' are selected:

Range	Jul-09				YTD
Form	V#	IV#	Total	Stat 1	V#	IV#	Total	Stat 1
T	78	102	180	24733	494	1087	1581	24733
O	199	133	332	32966	1242	2082	3324	32966

Help with set analysis

Fri, 18 Sep 2009 19:07:11 GMT

Actually what do you meant by value of month as eoy. Value of months can only be jan...dec, rite?

Help with set analysis

amit_shetty78 — Fri, 18 Sep 2009 19:27:23 GMT

It you see the table in the xls attached, the month column also takes value as 'eoy' and the corresponding dt value as blank.

Help with set analysis

Fri, 18 Sep 2009 21:04:29 GMT

So, the Stat1 value for the blank dimension is the value that it should be on the other two dimensions? Is Stat1 always the same like that? If so, could you implement a TOTAL into that formula? If the expression is Sum(value) change it to Sum(TOTAL value). That should give you:

Range	Jul-09				YTD
Form	V#	IV#	Total	Stat 1	V#	IV#	Total	Stat 1	V#	IV#	Total	Stat 1
T	78	102	180	24733	494	1087	1581	24733	0	0	0	24733
O	199	133	332	32966	1242	2082	3324	32966	0	0	0	32966

Then you just need a way to get those Stat 1s to be zero on the blank dimension and that whole dimension will be hidden (if suppress zero value is checked). You could use one of your other expressions for that, something like: If(V# Exp = 0, Sum(TOTAL value), 0).

That is very simplified, but it seems like it should work based on your results.