Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

How to fix pivot partial sums

Hi,

I have a pivot with three dimensions Version, Quarter and Month and a conditional expression as seen in gray areas they are actually calculated with below expression.

Is it possible to correct partial sums highligted red below? Any solution or suggestion is very helpful

My expression:

if(sum(QUANTITY)>0, sum(QUANTITY),  if(match(VERSION,'M1','M2')>0, RangeSum(Below(sum(QUANTITY),1,2))))

There are always six Versions and version order is fixed. If VERSION is 'M1' or 'M2' we get the Actuals value in to the cell and it is confusing the row total.

I attached the example qlikview application for problem with partial sums in the pivot.

Thanks

Ersen

1 Solution

Accepted Solutions
Not applicable

Re: How to fix pivot partial sums

I think there is no simple (even not possible) solution with pivot .

So far only solution to correct pivot sub totals is to introduce new records to the fact table within script.

pivot_sums_corrected.jpg

Basically the copying QUANTITY logic is handled as below: Now different sum expressions works and sub totals are correct in the pivot.

Thanks,

*********** code from script ***************

FACT:

LOAD if(VERSION='-', null(), Dual(VERSION,ORDER)) AS VERSION,
    
QUARTERMONTH, QUANTITY
FROM version_quantities.xls
(
biff, embedded labels);

NEW:
NoConcatenate
LOAD dual('M2',5) AS VERSION, VERSION AS ORIG_VERSION, QUARTER, MONTH, QUANTITY
Resident FACT
Where VERSION = 'Actuals'
and month(QuarterEnd(MONTH)) <> Month(MONTH)
;

Concatenate(NEW)
LOAD dual('M1',4) AS VERSION, VERSION AS ORIG_VERSION, QUARTER, MONTH, QUANTITY
Resident FACT
Where VERSION = 'Actuals'
and month(QuarterStart(MONTH)) = Month(MONTH)
;

Concatenate(FACT)
LOAD * Resident NEW;

drop Table NEW;

4 Replies
Not applicable

Re: How to fix pivot partial sums

No reply no suggestion ..

so it is not possible :-) ?

paulyeo11
Valued Contributor II

Re: How to fix pivot partial sums

Hi sir

I like to follow your post. No reply because yr expression is complicated.

I hv post few of these questions also not able get any reply.

Not applicable

Re: How to fix pivot partial sums

Thanks pauly I had provided example for this hopefully someone come up with some suggestion

lets see

E.

Not applicable

Re: How to fix pivot partial sums

I think there is no simple (even not possible) solution with pivot .

So far only solution to correct pivot sub totals is to introduce new records to the fact table within script.

pivot_sums_corrected.jpg

Basically the copying QUANTITY logic is handled as below: Now different sum expressions works and sub totals are correct in the pivot.

Thanks,

*********** code from script ***************

FACT:

LOAD if(VERSION='-', null(), Dual(VERSION,ORDER)) AS VERSION,
    
QUARTERMONTH, QUANTITY
FROM version_quantities.xls
(
biff, embedded labels);

NEW:
NoConcatenate
LOAD dual('M2',5) AS VERSION, VERSION AS ORIG_VERSION, QUARTER, MONTH, QUANTITY
Resident FACT
Where VERSION = 'Actuals'
and month(QuarterEnd(MONTH)) <> Month(MONTH)
;

Concatenate(NEW)
LOAD dual('M1',4) AS VERSION, VERSION AS ORIG_VERSION, QUARTER, MONTH, QUANTITY
Resident FACT
Where VERSION = 'Actuals'
and month(QuarterStart(MONTH)) = Month(MONTH)
;

Concatenate(FACT)
LOAD * Resident NEW;

drop Table NEW;

Community Browser