Sub Total Issues

[#1]

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

[Miguel_Angel_Baeyens]

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

[Gysbert_Wassenaar]

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.

[#4]

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.

[#5]

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.

[Gysbert_Wassenaar]

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.

[#7]

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

[#8]

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.