Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
musketeers
Creator
Creator

Quarterly Sum display at end of Quarter

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
Labels (2)
1 Solution

Accepted Solutions
justISO
Specialist
Specialist

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

View solution in original post

6 Replies
Chanty4u
MVP
MVP

musketeers
Creator
Creator
Author

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

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

justISO_0-1646751277190.png

 

musketeers
Creator
Creator
Author

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

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
Creator
Creator
Author

Hi, Thanks for your suggestion. It worked.