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

Display Total partial sum per quarter and end of month

qliknexus_kgcru — Thu, 28 Jun 2018 09:52:08 GMT

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!

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

sunny_talwar — Thu, 28 Jun 2018 12:55:09 GMT

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))

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

qliknexus_kgcru — Thu, 28 Jun 2018 13:33:47 GMT

‌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?

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

sunny_talwar — Thu, 28 Jun 2018 13:38:29 GMT

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

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))

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

qliknexus_kgcru — Fri, 29 Jun 2018 07:15:23 GMT

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!

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

sunny_talwar — Fri, 29 Jun 2018 12:31:11 GMT

Like this?

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

qliknexus_kgcru — Fri, 29 Jun 2018 15:23:18 GMT

‌yes! How did you do that?

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

sunny_talwar — Fri, 29 Jun 2018 15:25:20 GMT

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 — Fri, 29 Jun 2018 15:35:08 GMT

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

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

sunny_talwar — Fri, 29 Jun 2018 15:37:29 GMT

Awesome, I am glad I was able to help

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

qliknexus_kgcru — Mon, 02 Jul 2018 00:36:35 GMT

Thank you very much Sunny!!!