Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi Sunny,
This QuarterEnd should refer to which MonthYear of the quarter?
Regards,
Ruma
The maximum monthyear, right?
Do I need to write in this expression?
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)
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
I think it might help to look at a sample... can you provide one?
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
May be this
If(Mod(Month(MonthName), 3) = 0, Sum(Revenue) - RangeSum(After(Sum({<MonthName>}Revenue), 1, 2)), Sum(Revenue))