topic Re: Calculate a grand total in QlikView

Calculate a grand total

Anonymous — Thu, 02 Jun 2011 20:05:40 GMT

Agg_Pha_Tmp:

Load

MEM#,

sum(PhaTotal) as SumPhaTotal

Resident pha

group by MEM#;

Agg_Med_Tmp:

Load

MEM#,

sum(Total) as SumMedTotal

Resident med

group by MEM#;

I am wondering how i can sum the SumPhaTotal and SumMedTotal (SumPhaTotal + SumMedTotal) to get a GrandTotal.

Thanks.

Calculate a grand total

erichshiino — Thu, 02 Jun 2011 22:08:19 GMT

It could be like this:

Agg_Pha_Tmp:

Load

MEM#,

sum(PhaTotal) as SumPhaTotal

Resident pha

group by MEM#;

Agg_Med_Tmp:

Load

MEM#,

sum(Total) as SumMedTotal

Resident med

group by MEM#;

tAggr_Tmp:

Load MEM#, SumPhaTotal as SumTotal

resident Agg_Pha_Tmp;

concatenate (tAggr_Tmp)

Load MEM#, SumMedTotalas SumTotal

resident Agg_Pha_Med;

Aggr_Tmp:

Load MEM#, sum(SumTotal) as SumTotal

resident tAggr_Tmp

group by MEM#;

drop table tAggr_Tmp;

Hope this helps,

Erich

Re: Calculate a grand total

johnw — Thu, 02 Jun 2011 23:18:03 GMT

I'd load them as one table instead of two from the very start.

Data:
LOAD MEM#, sum(PhaTotal) as SumPhaTotal
RESIDENT pha
GROUP BY MEM#
;
OUTER JOIN (Data)
LOAD MEM#, sum(Total) as SumMedTotal
RESIDENT med
GROUP BY MEM#
;
LEFT JOIN (Data)
LOAD MEM#, rangesum(SumPhaTotal,SumMedTotal) as GrandTotal
RESIDENT Data
;

Re: Calculate a grand total

Anonymous — Fri, 03 Jun 2011 14:14:11 GMT

John,

Thank you. It works.