Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
SonnyDelete
Contributor
Contributor

Measure works with individual dates, but not months

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…

1.png

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

2.png

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

1 Solution

Accepted Solutions
borismichel
Luminary Alumni
Luminary Alumni

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?

 

View solution in original post

7 Replies
borismichel
Luminary Alumni
Luminary Alumni

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. 

 

dilipranjith
Partner
Partner

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

SonnyDelete
Contributor
Contributor
Author

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
Luminary Alumni
Luminary Alumni

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
Luminary Alumni
Luminary Alumni

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?

 

View solution in original post

SonnyDelete
Contributor
Contributor
Author

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

3.png

borismichel
Luminary Alumni
Luminary Alumni

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 🙂