Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Qlikview - Get Max Selected Day of Month

Hi Experts

I’ve the following situation:

Error1.PNG


- I’ve the Year, Month and Day selected because I only show the evolution until the day selected (in this case is 18 of Decemeber)


- If you repair the Chart don’t do any calculation after the selected day (only returns 0s), and it’s fine, but it still showing the days after 18. But I can Hide Zeros because I still want to show the days provided before the day selected.


What I want is the chart Like that (this in a example from Excel without values only to show the goal). Basically I want to get only the days before the day selected even If it contains 0s:


Erro2.PNG


I'm using some conditional expressions to play with Month, Year and Day but I can't get the chart I want


Here are my expressions for Teste1 (I take from another post where Sunny T post a similar thing):

  • Teste1
    • Conditional 1:
      • =GETSELECTEDCOUNT(YEAR) >= 1 and GETSELECTEDCOUNT(DAY) < 1
    • Expression:
      • =COUNT({<DATE = {"$(='>=' & Date(YearStart((DATE))) & '<=' & Date(Max(DATE)))"},YEAR,MONTH, DAY>} TESTE1)

      • Conditional 2:
        • =GetSelectedCount(DAY) >= 1
      • Expression:
        • =COUNT({<DATE = {"$(='>=' & Date(MonthStart((DATE))) & '<=' & Date(Max(DATE)))"},YEAR,MONTH, DAY>} TESTE1)

Thanks!!!!

8 Replies
swuehl
MVP
MVP

Maybe just add a field modifier for DAY:

=COUNT({<DATE = {"$(='>=' & Date(MonthStart((DATE))) & '<=' & Date(Max(DATE)))"},YEAR,MONTH, DAY = {"<=$(=Max(DAY))"} >} TESTE1)

Not applicable
Author

Swuehi, so many thanks!!!!!

Not applicable
Author

One more thing swuehl,

When I insert some more set analysis in the code I loose some days of my chart, imagine that:

=SUM({<DATE = {"$(='>=' & Date(MonthStart((DATE))) & '<=' & Date(Max(DATE)))"},YEAR,MONTH,DAY = {"<=$(=Max(DAY))"},FIELDX = {'X'}, DAY>} TESTE1) 

I'm loosing some days of my month... Do you know why?

swuehl
MVP
MVP

Not sure, hard to answer without knowing your data model and your data values a bit more detailed.

Probably the FIELDX values have not relations to all possible DAY values.

Try maybe something like

=SUM({<DATE = {"$(='>=' & Date(MonthStart((DATE))) & '<=' & Date(Max(DATE)))"},YEAR,MONTH,DAY = {"<=$(=Max(DAY))"},FIELDX = {'X'}, DAY>} TESTE1) 

+

SUM({<DAY = {"<=$(=Max(DAY))"}>} 0)

Peter_Cammaert
Partner - Champion III
Partner - Champion III

Why are there two set modifiers for field DAY in your expression? See the end of your set specification.

Not applicable
Author

I put the Day in the end to try to get the values that are losted... but probably is a problem related with my data model

Not applicable
Author

Swuehl, one more time thank you very much! You answer was perfect!!! Do you know why it happens without the second SUM()?

swuehl
MVP
MVP

You should only use a single modifier per field in a single set modifier <....>, so as a general rule, only use a FIELD Name once between a set of <....>. So please remove the last DAY in your expression, this may also resolve your issue (even without the second sum).

Good catch by Peter.