Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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_30 | 31_60_10 | 61_90_30 | 61_90_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!
