Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Tried to get sum for all full months as shown following:
Since 201405 has not reached to the end of the fiscal month yet. It should be ignored.
So ideally, I just want to have total of 1100 which is sum of months of 201403 and 201404 as following
For fiscal month, it is always 4 or 5 weeks.
I attached .qvw file. Please advise.
Thanks.
sum(aggr(if(count(total <YearMonth> DISTINCT Price)>=4,sum(Price)),Territory,YearMonth,Weekend_Ending))
Unless you add the logic that determines when a fiscal month starts and ends your requirement cannot be met.
Since I already know that a full month has to have at least 4 weeks, is it possible that I only sum when count reaches to 4?
No, because you said:
For fiscal month, it is always 4 or 5 weeks.
So, if you have four weeks, it's possible that for that particular month you should have five weeks for that month to be full. You supplied no information to determine when a month is full.
Can you suggest a solution if given 4 weeks? What should I put in expression?
Couldn't you enable the condition on the 'YearMonth' dimension:
Properties -> Dimension -> Check Enable Condition
Something like: Count(Weekend_End...) < 4
sum(aggr(if(count(total <YearMonth> DISTINCT Price)>=4,sum(Price)),Territory,YearMonth,Weekend_Ending))
Works great!!! Thank you