Quarterly Sum display at end of Quarter

[musketeers]

Hi, I have data like below(This is a example data): It has Month and Sales data. I need to display Sum of Sales per quarter and display it at end of quarter like it is displayed in "Quarterly Sum" column. I am using AGGR function but it displays SUM at start of the quarter. But I need to display at end of the quarter. I need to do these in Bar chart.

Month	Sales	quarterly Sum
202101	200
202102	100
202103	300	600
202104	500
202105	300
202106	200	1000
202107	600
202108	400
202109	100	1100
202110	400
202111	500
202112	400	1300

[Chanty4u]

Hi,

Just check this. 

https://community.qlik.com/t5/App-Development/Quarter-wise-sum-of-sales/td-p/1542159

its looks similar.

[musketeers]

Hi, The link shows solution in different way where for specific quarter it has specific expression. In my case, I have to display all months sales in bar chart and also in same bar chart I need to display sum of sales for a quarter in the end of quarter. So for 2021 Q1, I will have two bars for 202103 (displaying sales for Marcha nd sum of sales for this quarter) and for 202101 and 202102 I will have one bar displaying sales for that month.

[justISO]

Hi, you can try to add Quarter field from your [Month] field, for example at load script:

QuarterEnd (MonthEnd(Date(Date#([Month],'YYYYMM')))) as quarter

and in report level still use aggr, but with condition to show sum not at first, but on last quarter date:

if (MonthEnd(Date(Date#(Month,'YYYYMM'))) = quarter, sum(Aggr(nodistinct sum(Sales), quarter )))

[musketeers]

Hi, Thanks for this suggestion. It is working but when we have missing month then this is not working. For example if we are missing March month data or any quarter end month data then this solution will not work.

[justISO · Accepted solution]

Try with max date check:

if ( MonthEnd(Date(Date#(Month,'YYYYMM'))) = aggr(nodistinct max( MonthEnd(Date(Date#(Month,'YYYYMM')))), quarter), sum(Aggr(nodistinct sum(Sales), quarter )))

[musketeers]

Hi, Thanks for your suggestion. It worked.