Display Total partial sum per quarter and end of month

[qliknexus_kgcru]

Hi All,

I want to display the total partial sum in a cross table.

e.g. Here is my sample data:

Year	Month	Quarter	Sales	CATEGORY
2017	January	4	13000	A
2017	February	4	14000	B
2017	March	4	15000	A
2017	April	1	16000	B
2017	May	1	17000	A
2017	June	1	18000	B
2017	July	2	19000	A
2017	August	2	20000	B
2017	September	2	21000	A
2017	October	3	22000	B
2017	November	3	23000	A
2017	December	3	24000	B
2018	January	4	25000	A
2018	February	4	26000	B
2018	March	4	27000	A
2018	April	1	28000	B
2018	May	1	29000	A
2018	June	1	30000	B
2018	July	2	31000	A
2018	August	2	32000	B
2018	September	2	33000	A
2018	October	3	34000	B
2018	November	3	35000	A
2018	December	3	36000	B

I have selected YEAR=2018 and MONTH=MARCH and it  will show me the selected month, the previous 6 months and the next 5 months like so based on my expression:

Year	Month	Quarter	Sales	CATEGORY
2017	September	2	21000	A
2017	October	3	22000	B
2017	November	3	23000	A
2017	December	3	24000	B
2018	January	4	25000	A
2018	February	4	26000	B
2018	March	4	27000	A
2018	April	1	28000	B
2018	May	1	29000	A
2018	June	1	30000	B
2018	July	2	31000	A
2018	August	2	32000	B

I need to put it in a cross table like this, where it will get the total for the quarter if whole 3 months (Oct, Nov, Dec; Jan, Feb,Mar) are present and if the last month (Sep) of the quarter is present. But, if it only displays the first 2 months (Jul, Aug) of the quarter, it will not show the total:

I managed to put it in a cross table below, but i'm having a hard time with the total:

Thank you for any help!

[sunny_talwar]

Check this

=If(SecondaryDimensionality() = 2 and Count(TOTAL <FiscalYear, Quarter> DISTINCT {<YearMonth = {"$(='>=' & Date(MonthStart(Max(YearMonth), -6), 'YYYYMM') & '<=' &Date(MonthStart(Max(YearMonth), 5), 'YYYYMM'))"}, Year, Month, Quarter>} YearMonth) <> 3 and

  Max(TOTAL <FiscalYear, Quarter> {<YearMonth = {"$(='>=' & Date(MonthStart(Max(YearMonth), -6), 'YYYYMM') & '<=' &Date(MonthStart(Max(YearMonth), 5), 'YYYYMM'))"}, Year, Month, Quarter>} YearMonth) <>

  MonthStart(QuarterEnd(Max(TOTAL <FiscalYear, Quarter> {<YearMonth = {"$(='>=' & Date(MonthStart(Max(YearMonth), -6), 'YYYYMM') & '<=' &Date(MonthStart(Max(YearMonth), 5), 'YYYYMM'))"}, Year, Month, Quarter>} YearMonth))), 0,

Sum({<YearMonth = {"$(='>=' & Date(MonthStart(Max(YearMonth), -6), 'YYYYMM') & '<=' &Date(MonthStart(Max(YearMonth), 5), 'YYYYMM'))"}, Year, Month, Quarter>}Sales))

[qliknexus_kgcru]

‌Hi,

Thank you for your answer. Is it possible for the Q2 total to have the total not only for Sep, but also includes Jul, Aug, & Sep total?

[sunny_talwar · Accepted solution]

Try this

=If(SecondaryDimensionality() = 2,

If(Count(TOTAL <FiscalYear, Quarter> DISTINCT {<YearMonth = {"$(='>=' & Date(MonthStart(Max(YearMonth), -6), 'YYYYMM') & '<=' &Date(MonthStart(Max(YearMonth), 5), 'YYYYMM'))"}, Year, Month, Quarter>} YearMonth) <> 3 and

  Max(TOTAL <FiscalYear, Quarter> {<YearMonth = {"$(='>=' & Date(MonthStart(Max(YearMonth), -6), 'YYYYMM') & '<=' &Date(MonthStart(Max(YearMonth), 5), 'YYYYMM'))"}, Year, Month, Quarter>} YearMonth) <>

  MonthStart(QuarterEnd(Max(TOTAL <FiscalYear, Quarter> {<YearMonth = {"$(='>=' & Date(MonthStart(Max(YearMonth), -6), 'YYYYMM') & '<=' &Date(MonthStart(Max(YearMonth), 5), 'YYYYMM'))"}, Year, Month, Quarter>} YearMonth))), 0,

  Sum({<YearMonth = {"$(='>=' & Date(QuarterStart(MonthStart(Max(YearMonth), -6)), 'YYYYMM') & '<=' &Date(MonthStart(Max(YearMonth), 5), 'YYYYMM'))"}, Year, Month, Quarter>}Sales)),

Sum({<YearMonth = {"$(='>=' & Date(MonthStart(Max(YearMonth), -6), 'YYYYMM') & '<=' &Date(MonthStart(Max(YearMonth), 5), 'YYYYMM'))"}, Year, Month, Quarter>}Sales))

[qliknexus_kgcru]

Hi Sunny,

Thank you so much for your answer! it worked well! one more thing, I've tried to hide the Fiscal Year and Quarter column from the pivot table but it seems to mess up the calculation. can i hide the Fiscal Year and Quarter column while retaining the correct values of the table? thanks!

[sunny_talwar]

Like this?

[qliknexus_kgcru]

‌yes! How did you do that?

[sunny_talwar]

I have already attached the qvw sample with my response above... did you get a chance to look at it?

Re: Display Total partial sum per quarter and end of month

[qliknexus_kgcru]

‌yes i have checked it, it works well for me.

[sunny_talwar]

Awesome, I am glad I was able to help