Sum only for full fiscal month

[#1]

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.

[Gysbert_Wassenaar]

Unless you add the logic that determines when a fiscal month starts and ends your requirement cannot be met.

[#3]

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?

[Gysbert_Wassenaar]

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.

[#5]

Can you suggest a solution if given 4 weeks? What should I put in expression?

[joshabbott]

Couldn't you enable the condition on the 'YearMonth' dimension:

Properties -> Dimension -> Check Enable Condition

Something like: Count(Weekend_End...) < 4

[Gysbert_Wassenaar · Accepted solution]

sum(aggr(if(count(total <YearMonth> DISTINCT Price)>=4,sum(Price)),Territory,YearMonth,Weekend_Ending))

[#8]

Works great!!! Thank you