topic Re: Sub Total Issues in QlikView

Sub Total Issues

Tue, 18 Dec 2012 09:11:06 GMT

Hi Guys,

Hope you can help me figure out how to display my sub-total correctly or as per my requirement:

Supposed i have the following table:

Month	Group	Year	Quantity	Amount
JAN	G1	2012	10	100
JAN	G1	2013	10	100
JAN	G2	2012	10	100
JAN	G2	2013	10	100
	TOTAL		40	400

The above us the total of Qlikview (Default)

But what I want is to include subtotal per year like below:

Month	Group	Year	Quantity	Amount
JAN	G1	2012	10	100
JAN	G1	2013	10	100
JAN	G2	2012	10	100
JAN	G2	2013	10	100
	TOTAL	2012	20	200
	TOTAL	2013	20	200

Is this possible? Then after this the Grand Total should also have 2012 and 2013...

Thanks in advance...

I also attached the QVW if anyone wanted to play along with...

~skip

Re: Sub Total Issues

Miguel_Angel_Baeyens — Tue, 18 Dec 2012 09:25:38 GMT

Hi,

You can add a new Show Partial Sums in the Presentation tab of the chart properties to the field FYEAR, or create a new expression, but that will make your chart different. Subtotals are referred to dimension values, you cannot mix them, or "insert a new line" as the expressions make sense related to the dimensions.

Hope that helps.

Miguel

Re: Sub Total Issues

Gysbert_Wassenaar — Tue, 18 Dec 2012 09:27:37 GMT

Try adding the totals to the table:

T1:

LOAD [Item Final Group],

FYEAR,

AMOUNT,

QTY,

MONTH

FROM YOY_TOTAL.QVD (qvd);

Concatenate

load 'TOTAL' as [Item Final Group], 'TOTAL' as MONTH, FYEAR, sum(AMOUNT) as AMOUNT, sum(QTY) as QTY

Resident T1 group by FYEAR;

See attached example.

Re: Sub Total Issues

Tue, 18 Dec 2012 09:29:24 GMT

Hi Miguel,

Thanks for the info, I also tried that but the thing I need to know the subtotal per year, Is there any other way to do it...

My current workaround is to summarize the data and concatenate it altering the MONTH, ITEM AND YEAR dimensions.

Re: Sub Total Issues

Tue, 18 Dec 2012 09:30:26 GMT

Hi Gysbert,

thank you for your reply, this is actually what Im doing but i can't always redo this and then i can no longer use the builtin sub total since it will give me incorrect information for the GRAND total.

Re: Sub Total Issues

Gysbert_Wassenaar — Tue, 18 Dec 2012 10:24:08 GMT

I don't understand what you mean. The totals are recalculated on every reload so they are always correct. Just don't use the grand total in the chart (disable partial sums on the first dimension) since you've already calculated your own in the script.

I'm afraid it's either this solution or something far more messy (if anything at all). As Miguel explained you can't mix subtotals the way you want.

Re: Sub Total Issues

Thu, 20 Dec 2012 01:12:21 GMT

Hi Gysbert,

The thing is for every summary that the user want I need to create each of them separate chart/pivot table.

Becuase if the user wanted to select only particular month then my computed grand total on reload will already be wrong.

For illustration purposes please see attached screenshots.

Thanks again and I think I will just leave with my current workaround.

Regards,

~skip

Re: Sub Total Issues

Thu, 20 Dec 2012 09:30:45 GMT

hi !!

I donnw Whethr i have understood this completely...and can't see ur Qvw file as m using my personal edition........but if its only regarding getting the subtotals on yearly basis....then y not try pivoting the chart and shifting the Year column towards the left and the Group column towards right

Then the subtotals can be seen on Yearly basis if you take year as dimension and the grand total too as you also have mnth as dimension.

this was jsst a simple suggession, ignore it.. if the desired result is smthng different.