Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Greetings,
I am having a problem with a Pivot table and groupings using set analysis. I have attached a file that demonstrated the problem and any help will be appreciated.
You can see from the file I have 3 dimensions in a pivot table:
I am totalling the counts of the 'Type' field by Year and SGroup. To do further analysis of the data I also need the overall total for the PGroup, by year, for each SGroup.
To keep things looking neat I want to select only one SGroup. For instance, if I select 'a1' I would want to see the row for the other SGroups (a2, a3) disappear but leave the overall yearly total for the PGroup intact.
However what is happening is that the rows for a2 & a3 remain with a 0 for the SGroup total and the PGroup total populated on every line.
Current (with no SGroup selection):
2010 2011
-------------------------------------------------------------------
SGroup Total SGroup Year Total SGroup Year
-------------------------------------------------------------------
a1 2 3 1 4
a2 1 3 2 4
a3 0 3 1 4
Desired (after selecting a1):
2010 2011
-------------------------------------------------------------------
SGroup Total SGroup Year Total SGroup Year
-------------------------------------------------------------------
a1 2 3 1 4
Is there any way to achieve this?
Many thanks!
Hi,
you could achieve this by putting a condition on your "Year" total field.
For example:
=if(count(total <SGroup> Type)=0,0,
count({$<PGroup={$(=Only(PGroup))},SGroup=>}TOTAL<Year> Type))
As suppress zeroes is ticked on the Presentation tab this will giv eyou the result you are looking for.
I had a similar problem not so long back.
I found that if I viewed before I qliked it seemed to work
I am not sure... I generally Qlik before I View.
Seriously, any proper suggestions out there?
Hi,
you could achieve this by putting a condition on your "Year" total field.
For example:
=if(count(total <SGroup> Type)=0,0,
count({$<PGroup={$(=Only(PGroup))},SGroup=>}TOTAL<Year> Type))
As suppress zeroes is ticked on the Presentation tab this will giv eyou the result you are looking for.
That most defiantly works in the example!
Now I’ll plug it into my main application.
Thanks for that, most useful!