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