Summarising set analysis in a pivot table

adamdavi3s — Fri, 18 May 2012 09:22:03 GMT

Hi All,

I have a 'bucketed' report based on £ which we have been using for some time and works great. However I now need to produce a table / chart which displays the NUMBER of items in each bucket.

The buckets are pre-calculated in SQL in a couple of different variations so in qlikview the user can quickly change the basis that the buckets are calculated upon (in our case invoice date + 30 days or invoice date +10 days) (Note there are additional complexities to this) so for example the data looks like this:

sup_group	pspp_voucher_no	Ontime_10	Ontime_30	1_30_30	1_30_10	31_60_10
NHS	12345			£500		£500
NHS	56895		£12.12		£12.12
NON	12365	£100	£100

Which works perfectly in the dashboard:

The buckets are each controled by a variable depending on the selections the users make, but all the variable does is tell the system which column to sum, so for example:

The ontime column is simply:

=sum($(vap_pspp_ontime))

The variable states:

=if(vap_pspp_calculationtype='Invoice Date',
    if(vap_pspp_calculation_days=10, 'pspp_10_invoice_ontime','pspp_30_invoice_ontime'),
    if(vap_pspp_calculationtype='Invoice Received Date',if(vap_pspp_calculation_days=10, 'pspp_10_received_ontime','pspp_30_received_ontime'),
                                                if(vap_pspp_calculation_days=10, 'pspp_10_all_ontime','pspp_30_all_ontime')))

So in my new table, I have the column to calculate the number of invoices is:

=if(sum($(vap_pspp_ontime))<>0,count(distinct pspp_voucher_no),0)

And this works great at the lowest level:

But you can see the totals are giving the same answer, which is the case when I change this to a pivot table to summarise the data:

Now I have tried to use the aggr function, but I don't think this will work as even a basic sum will not work.

I tried various iterations including:

=sum(if(sum($(vap_pspp_1_30))<>0,1,0))  doesn't calculate
=if(sum($(vap_pspp_1_30))<>0,1,0)  just gives 1 at the highest level

Any suggestions warmly welcomed!

Summarising set analysis in a pivot table

adamdavi3s — Fri, 18 May 2012 09:46:41 GMT

I noticed when exported to excel the calculated columns are showing as text.

Therefore I changed the display type to number, and also tried wrapping it in a num#() but that didn't work... but it does explain why the sum doesn't work....

Summarising set analysis in a pivot table

adamdavi3s — Thu, 24 May 2012 13:25:40 GMT

anyone?

Summarising set analysis in a pivot table

adamdavi3s — Thu, 07 Jun 2012 13:25:10 GMT

help!

topic Summarising set analysis in a pivot table in QlikView

Summarising set analysis in a pivot table

Summarising set analysis in a pivot table

Summarising set analysis in a pivot table

Summarising set analysis in a pivot table