topic Re: Set Analysis to exclude particular column row value in QlikView

Set Analysis to exclude particular column row value

Anonymous — Sun, 08 Feb 2015 19:39:53 GMT

Hi Friends,can any one please help on set Analysis Issue facing .My scenario is how to exclude particular row in calculations below st. table need to sum up A-D as Total in Grade Column.Below is the Expression for Item Diff .

vItemDiff= if (count ([Final Indicator])=0,' ',(1+if (Sum([Actual X Sales]) = 0,' ',\ Sum ([Actual R Sales])/sum([Actual X Sales])-1))/(1+ if (SUM ([WgtsX Sales])=0,' ', SUM ([Wgts R Sales])/SUM ([WgtsX Sales])-1))-1)

O/P

Grade	Item Diff
A	21
B	3
C	4
D	4
No Master	11
Total A-D	43

Desired Expected Output:

Grade	Item
A	21
B	3
C	4
D	4
No Master	11
Total A-D	32

Re: Set Analysis to exclude particular column row value

Peter_Cammaert — Sun, 08 Feb 2015 20:32:00 GMT

Change your expression into something like:

=if (dimensionality() = 0, NEWEXPRESSIONWITHSETANALYSIS, $(vItemDiff))

This will select a different expression whenever a total is being calculated.

What will NEWEXPRESSIONWITHSETANALYSIS become? The same expression as you listed above, except that every aggregation function should contain a set spec like:

... {<Grade = {"A", "B", "C", "D"}>} ...

Best,

Peter

Re: Set Analysis to exclude particular column row value

Anonymous — Mon, 09 Feb 2015 12:20:00 GMT

can you please explain in clear case.In above solution you provided not getting exactly in normal to exclude in set analysis we use-= in set

Re: Set Analysis to exclude particular column row value

Peter_Cammaert — Mon, 09 Feb 2015 12:35:05 GMT

Then use a comparable set modifier that starts from the current selection:

... {<Grade -= {"No Master"}>} ...