Maximum Month per Quarter in Trending Chart

[nisha_rai]

Hi,

I want to show Trending bar chart where in each Quarter we have to pick the maximum Month of each Quarter.

For Example:

2017,Q1,Jan,100

2017,Q1,Feb,100

2017,Q1,Mar,100

2016,Q4,Oct,200

2016,Q4,Nov,200

2016,Q4,Dec,200

2016,Q3,July,300

2016,Q3,Aug,300

2016,Q3,Sep,300

as per the data we have to show the bar ghaph where Quarter is our dimension but we have to show sum of the sales where month is maximum based on quarter . As well as i need to show the Comparison based on the Current Quarter and Previous Quarter but when the user select any month that time it will pick the maximum month of the previous quarter.

Please help me to resolve the issue

Regards,

Nisha

[Anil_Babu_Samineni]

Is this you are expecting

Or this?

[kaushiknsolanki · Accepted solution]

Hi,

What you can do is create a flag in mastercal to identify the maximum month of the quarter, like below.

Load Date,

        if(Monthname(Date) = Monthname(quarterend(Date)),1,0) as QuarterMaxMonthFlag

From xyz;

Now you can use this flag in set analysis to get sales of maximum month.

Sum({<QuarterMaxMonthFlag = {1}>}Sales)

Regards,

Kaushik Solanki

[isorinrusu]

Based on Kaushik's answer, and knowing it has a worse performance, what you can do is the following:

sum(Aggr(

if(Date=QuarterEnd(Date),sum(Value),0)

,Date))

For that, you must have your Date in a date format (DD/MM/YYYY or whatever).

But the flag option is way better: aggrs and ifs are no good.

Regards,

Sorin.