Measure works with individual dates, but not months

[SonnyDelete]

Hi All,

I’m having an issue with my measures and how they interact with date filters for previous year calculations.

Here’s one measure, which is intended to total the number of consignments in the previous year based on the selected date range…

Count({<Date={">=$(=Min(AddYears(Date,-1)))<=$(=Max(AddYears(Date,-1)))"}>}[Consignment Reference])

This works when I select individual dates…

However, If I select an entire month it breaks the measure…

Am I missing something obvious here?

Here are the relevant dates from my master calendar...

[Date ID] as Date
Date(Monthstart([Date ID]), 'MMM-YYYY') as 'Month-Year'

Hopefully someone can see where I’m going wrong?

Many thanks,
Sonny

[borismichel]

Hi Sonny,

I believe the problem is the following:

 It works in the first case, because you overwrite any selections you make in the date field with the result of your set expression calculations. That's why it works if you make selections in the date field.

Also this means if you select in Month-Year you create an associated set in the field Date which is 1st through 31st January 2019. The problem now is that your set expression does not find any matching values anymore, since you only look for a subset of your current selection space - which is 01-2019. In layman's terms, your expression only looks in the white values. 

[dplr-rn]

Try your expression with
Count({<Month-Year=, Date={">=

[SonnyDelete]

Thanks for the reply Borismichel.
Do you know of a better way to do this?
I'd like the user to be able to select whatever date range they want, but obviously it's a bit of a chore to have to pick each date individually, so wanted to give them the ability to choose whole months.

[borismichel]

Yea, providing a solution would have been helpful. You will either need to unset any selections you make in any other date field you use, so add:

Month-Year=, Date={...

to your set expression.

You'll need to to this with every field that is related to Date.

[borismichel · Accepted solution]

Hi Sonny, 

the issue will always be that the set of data you evaluate in your set expressions will be limited to your current selections (with the $ set identifier). This means you'll need to design your application so that you either add all other date related selection fields to your set expression (as above, except the one you're running the expression in). If you want to create a more comfortable way to do this maybe try it like this:

 Add this to your script:

12*Year(Today())+Month(Today())-12*Year(Date)-Month(Date) AS [MonthsAgo] 

I've stolen this from Qlik's autocalendar, don't sue me :).

This will give you a MonthsAgo flag connected to your date. This means you can use simple integers in your date relations last year would be MonthsAgo={12} in your Set. You could do it with years, but this will also allow you to do month over month and same period over same period etc. This eliminates the problem that you need to have the Date field in there and the complex calculation (It also requires you to only want to select full months ).

So your expression would look something like this 

Count({<Month-Year=, MonthsAgo={12}>} [Consignment ...]) 

Does this help?

[SonnyDelete]

Perfect! Thanks Borismichel (and Dilipranjith - sorry, didn't see your response before my last post).

I'll try your alternative approach too. Sounds much cleaner!

Really appreciate the help.

Thanks,
Sonny

[borismichel]

I made a mistake, for it to be rolling from your selection - and not only 12 months before today - you need to pass the selected value:

Count({<Month-Year=, MonthsAgo={$(=[MonthsAgo]+12)}>} [Consignment ...])

Happy to help 🙂