Nested functions in set analysis

[#1]

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?

[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

[#4]

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)

[#6]

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)

[#8]

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?

[#10 · Accepted solution]

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.