topic Pivot table groups, totals and set analysis. in QlikView

Pivot table groups, totals and set analysis.

thomastc — Mon, 06 Jun 2011 10:44:55 GMT

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!

Pivot table groups, totals and set analysis.

Mon, 06 Jun 2011 12:32:49 GMT

I had a similar problem not so long back.

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

Pivot table groups, totals and set analysis.

thomastc — Mon, 06 Jun 2011 12:47:36 GMT

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

Seriously, any proper suggestions out there?

Pivot table groups, totals and set analysis.

pat_agen — Mon, 06 Jun 2011 13:21:22 GMT

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.

Pivot table groups, totals and set analysis.

thomastc — Mon, 06 Jun 2011 13:34:07 GMT

That most defiantly works in the example!

Now I’ll plug it into my main application.

Thanks for that, most useful!