11 Replies Latest reply: Jun 19, 2017 7:51 AM by Sunny Talwar

# Set Analysis in finding measure of quarter end month

Hi All,

I want to find the sum of revenue for each quarter(taken as dimension) but the measure should not be the sum of three months of the quarter but the maximum month of the respective quarter..

How to achieve that?

Regards,

Ruma

• ###### Re: Set Analysis in finding measure of quarter end month

May be like this:

Sum({<MonthYear = {"=MonthYear = MonthStart(QuarterEnd(MonthYear))"}>}Revenue)

Where MonthYear is created like this in the script

• ###### Re: Set Analysis in finding measure of quarter end month

Hi Sunny,

This QuarterEnd should refer to which MonthYear of the quarter?

Regards,

Ruma

• ###### Re: Set Analysis in finding measure of quarter end month

The maximum monthyear, right?

• ###### Re: Set Analysis in finding measure of quarter end month

Do I need to write in this expression?

• ###### Re: Set Analysis in finding measure of quarter end month

This goes in the script (if you don't already have it or something similar)

Sum({<MonthYear = {"=MonthYear = MonthStart(QuarterEnd(MonthYear))"}>}Revenue)

• ###### Re: Set Analysis in finding measure of quarter end month

Hi Sunny,

One more doubt.

Suppose m finding the revenue taking monthname(MMM YYYY) as dimension and I am doing some calculation like when the months are Mar,June,Sep and Dec(Quarter Months).I am deducting the revenue of those months from previous two months like if it's March I should deduct revenue of Feb and Jan .For this I have created variables but the thing is that I have data for three years so when I am doing March-FebJan, it is doing for three years,How to make it work for individual year?

Regards,

Ruma

• ###### Re: Set Analysis in finding measure of quarter end month

I think it might help to look at a sample... can you provide one?

• ###### Re: Set Analysis in finding measure of quarter end month

Hi Sunny,

Attaching one sample data.The thing what I want to achieve is :

When the month is Mar,June,Sep and Dec,I want to deduct the revenue from the previous two months.

Eg: for monthname Mar 2017,[revenue (Mar'17)-revenue(jan'17 & feb'17)]

for Dec 2016 [revenue (Dec'16)-revenue(oct'16 & Nov'16)] ,n so on.

How to achieve this?

Regards,

Ruma

• ###### Re: Set Analysis in finding measure of quarter end month

May be this

If(Mod(Month(MonthName), 3) = 0, Sum(Revenue) - RangeSum(After(Sum({<MonthName>}Revenue), 1, 2)), Sum(Revenue))