Pivot table groups, totals and set analysis.

[thomastc]

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:

• Year,
• PGroup (Parent group),
• SGroup (Sub group)

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!

[#2]

I had a similar problem not so long back.

I found that if I viewed before I qliked it seemed to work

[thomastc]

I am not sure... I generally Qlik before I View.

Seriously, any proper suggestions out there?

[pat_agen · Accepted solution]

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.

[thomastc]

That most defiantly works in the example!

Now I’ll plug it into my main application.

Thanks for that, most useful!