Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
May be a missed parenthesis at the end?
Sum({<[Date]= {"$(=AddMonths(Max({<Subscriptions = {'>0'}>}Date), -1))"}>}Subscriptions)
Date format might be an issue as well. Look here for help with Dates in Set Analysis
Thank you, I tried that but it did not work.
What is your date format? May be try like this:
Sum({<[Date]= {"$(=Date(AddMonths(Max({<Subscriptions = {'>0'}>}Date), -1), 'DateFieldFormatHere'))"}>}Subscriptions)
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
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)
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.
Would you be able to share a sample for us to play around with?
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.