Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Discover how organizations are unlocking new revenue streams: Watch here
cancel
Showing results for 
Search instead for 
Did you mean: 
cata_pli
Partner - Contributor III
Partner - Contributor III

Pivot table with inter-row set analysis calculation

Hello, I have this input data:

DayItemAmountView
d1111
d1211
d13 2
d2111
d2211
d23 2


I want a pivot table with Item and Day as Dimensions and a measure where for Item=3, I get the sum of Amount for Item 1 and 2, and for Item 1 and 2, normal Sum(Amount). I am using this formula:

If(Item=3, Sum({<Item={1,2}, View=>}total <Day> Amount), Sum(Amount))

I am using View=, because I want to be able to filter what I see in the table (that is to see only Item 3 when I apply a filter (2) on View, but still get the values from item 1 and 2.

The formula works when I don't apply filter on View=2, but when I do apply this selection on View filter I still see in the table the rows for Item 1 and 2 with 0 values, even though, I thought the filter View=2 would exclude them.  I know I am using View= in formula, but that is only for Item 3, not the first two rows. 

Why are this rows still there? Is there a way to see only Item 3 when I filter View=2 ?

ItemDayd1d2
1 00
2 00
3 22

I cannot use above(), or Supress 0 values. It has to be Set Analysis because in the real data model I will not know how many rows above to go, and I need to see zeroes.

I attach sample app.

Thank you for your time !

 

 

Labels (2)
0 Replies