Set Analysis in finding measure of quarter end month

[ruma_barman]

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

[sunny_talwar]

May be like this:

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

Where MonthYear is created like this in the script

LOAD Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear

[ruma_barman]

Hi Sunny,

This QuarterEnd should refer to which MonthYear of the quarter?

Regards,

Ruma

[sunny_talwar]

The maximum monthyear, right?

[ruma_barman]

Do I need to write in this expression?

[sunny_talwar]

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

LOAD Date(MonthStart(TempDate), 'MMM-YYYY') as MonthYear

This as your expression

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

[ruma_barman]

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

[sunny_talwar]

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

[ruma_barman]

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

[sunny_talwar]

May be this

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