Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Help with set analysis

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.

5 Replies
Highlighted
Not applicable

Help with set analysis

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)

Highlighted
Creator II
Creator II

Help with set analysis

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

RangeJul-09YTD
FormV#IV#TotalStat 1V#IV#TotalStat 1V#IV#TotalStat 1
T78102180049410871581000024733
O1991333320124220823324000032966


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:

RangeJul-09YTD
FormV#IV#TotalStat 1V#IV#TotalStat 1
T78102180247334941087158124733
O1991333323296612422082332432966


Highlighted
Not applicable

Help with set analysis

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

Help with set analysis

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

Highlighted
Not applicable

Help with set analysis

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:

RangeJul-09YTD
FormV#IV#TotalStat 1V#IV#TotalStat 1V#IV#TotalStat 1
T7810218024733494108715812473300024733
O199133332329661242208233243296600032966


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.