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

Nested functions in set analysis

I want to create a measure that sums the subscriptions for the previous month.

I currently use this expression to find the subscribers in the current month:

sum({<[Date]= {"$(=Max({<Subscriptions = {">0"}>} Date))"}>}Subscriptions)

(The reason I have filter only months with subscribers > 0 is that I have future dates in my data)

This expression correctly returns the previous month:

AddMonths(Max({<Subscriptions = {">0"}>}Date), -1)

However, I am not able get it working when trying to combine these expressions into:

sum({<[Date]= {"$(=AddMonths(Max({<Subscriptions = {">0"}>}Date), -1)"}>}Subscriptions)


I have tried several different syntaxes without luck. I suspect it is because of the nested functions?

Can anyone help me on the way?

1 Solution

Accepted Solutions
Not applicable
Author

I have solved it!

The problem was that the subscriber count is recorded at the last day of each month, so when subtracting 1 month, I could get a date which had no subscriber data.

E.g. when subtracting 1 month from the date 30.11.2016, I will get 30.10.2016, but since October has 31 days, I get zero subscribers for that date.

I solved it by using the MonthEnd function, so my expression to find the correct date would be:

MonthEnd(AddMonths(Max({<Subscriptions = {'>0'}>}Date), -1))


I guess I also could have solved it by using a YearMonth-field instead of full date field in my expression.

Thank you for all your help Sunny T.

View solution in original post

9 Replies
sunny_talwar

May be a missed parenthesis at the end?

Sum({<[Date]= {"$(=AddMonths(Max({<Subscriptions = {'>0'}>}Date), -1))"}>}Subscriptions)

sunny_talwar

Date format might be an issue as well. Look here for help with Dates in Set Analysis

Not applicable
Author

Thank you, I tried that but it did not work.

sunny_talwar

What is your date format? May be try like this:

Sum({<[Date]= {"$(=Date(AddMonths(Max({<Subscriptions = {'>0'}>}Date), -1), 'DateFieldFormatHere'))"}>}Subscriptions)

Not applicable
Author

I tried this with no luck:

Sum({<[Date]= {"$(=Date(AddMonths(Max({<Subscriptions = {'>0'}>}Date), -1), 'DD.MM.YYYY'))"}>}Subscriptions)

I have confirmed that this expression gives the correct date output:

AddMonths(Max({<Subscriptions = {">0"}>}Date), -1) = 30.11.2016

And that this gives the correct subscriber amount for the current month:

sum({<[Date]= {"$(=Max({<Subscriptions = {">0"}>} [Date]))"}>}Subscriptions) = XXX subscribers

sunny_talwar

Have you made selection in Month or MonthYear field? You might have to ignore selections in those fields

Sum({<[Date]= {"$(=Date(AddMonths(Max({<Subscriptions = {'>0'}>}Date), -1), 'DD.MM.YYYY'))"}, Month, MonthYear, Year>}Subscriptions)

Not applicable
Author

I tried that, but it did not do any difference. I also want the measure to be dynamic, so that the max(date) should be the max(date) in the current selection.

I just found out that the original expression works correctly when only two months are selected, but it gives 0 when more than two months are selected:

Sum({<[Date]= {"$(=AddMonths(Max({<Subscriptions = {'>0'}>}Date), -1))"}>}Subscriptions)

The expression I use to get subscriptions for current month however, works with all selections:

sum({<[Date]= {"$(=Max({<Subscriptions = {">0"}>} Date))"}>}Subscriptions)

They are very similar, so it is strange they behave so differently.

sunny_talwar

Would you be able to share a sample for us to play around with?

Not applicable
Author

I have solved it!

The problem was that the subscriber count is recorded at the last day of each month, so when subtracting 1 month, I could get a date which had no subscriber data.

E.g. when subtracting 1 month from the date 30.11.2016, I will get 30.10.2016, but since October has 31 days, I get zero subscribers for that date.

I solved it by using the MonthEnd function, so my expression to find the correct date would be:

MonthEnd(AddMonths(Max({<Subscriptions = {'>0'}>}Date), -1))


I guess I also could have solved it by using a YearMonth-field instead of full date field in my expression.

Thank you for all your help Sunny T.