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
Don't miss the upcoming Q&A with Qlik session on Qlik Application Automation on November 16th! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Qlik_Student7
Contributor
Contributor

Get last month with value

Hello,

 

I want to only show the value of the last month where ID was entered.

I used the following expression.

count({<YEAR_MONTH= {$(=max(YEAR_MONTH))} >} distinct ID)

but I don't get any value.

I suspect that the reason is that this expression generally shows the last month in the data and I need one that shows the last month that has value.

Thank you in advance 🙂

Labels (2)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

Max() returns a numeric value, but SET modifiers are comparing the string value.

You are trynig to achieve this output with your dollar expansion: 

count({<YEAR_MONTH= {'2019-Oct'} >} distinct ID)

 

Take look at this image that is showing the problem with max() on a dual.

Vegar_1-1627568745550.png

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

4 Replies
Vegar
MVP
MVP

It could be the modifier that is wrong. If YEAR_MONTH is a dual then you would need to format your values in the same format as the field. 

Eg: your YEAR_MONTH is in the format "YYYY MM" then your set could look like this:

count({<YEAR_MONTH= {$(=date(max(YEAR_MONTH),'YYYY MM')} >} distinct ID)

Plees ekskuse my Swenglish and or Norweglish spelling misstakes
Qlik_Student7
Contributor
Contributor
Author

I'm compering a column to itself, would the max function change the format?

Vegar
MVP
MVP

Max() returns a numeric value, but SET modifiers are comparing the string value.

You are trynig to achieve this output with your dollar expansion: 

count({<YEAR_MONTH= {'2019-Oct'} >} distinct ID)

 

Take look at this image that is showing the problem with max() on a dual.

Vegar_1-1627568745550.png

 

Plees ekskuse my Swenglish and or Norweglish spelling misstakes

View solution in original post

Qlik_Student7
Contributor
Contributor
Author

Interesting, thank you so much ☺️