Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum only for full fiscal month

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.

1 Solution

Accepted Solutions
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand

View solution in original post

7 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

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.


talk is cheap, supply exceeds demand
Not applicable
Author

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

joshabbott
Creator III
Creator III

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

Properties -> Dimension -> Check Enable Condition


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

Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
Not applicable
Author

Works great!!! Thank you