Min/Max Date function in load script

[philip_radovich]

Hello,

I am currently having some trouble trying to write an expression to display the Minimum Quarter, Maximum Quarter, and the Maximum Quarter -1. I am not sure if I need to use set analysis or if there is a function to do this for me. I need to set them in the load script as fields because things become cluttered in my pivot table when adding as measures in the table.  I currently in my load script have this:

   QuarterName(AssessmentDate) AS Quarter,

    Year(AssessmentDate) AS Year,

    QuarterStart(AssessmentDate) AS InitialQuarter,

    QuarterEnd (AssessmentDate) AS CurrentQuarter,

When building my visual though it lists every quarter instead of a min and max and I cant seem to get any of the scripts I have tried to give me exactly what I need. I am fairly new to qlik sense so I may be completely off on how I am supposed to do this. Any advice would help.

Thanks,

Philip

[migueldelval]

Hi Philip,

I think that it´s better to use variables. For Example:

Let vInitialQ: max(week(today()))

LET vStartQ: min(week(today()))

LET vEndQ: max(week(today())) -1

And you can use the variables to your expression. Try it and tell me if you want.

Regards

Miguel del Valle

[philip_radovich]

Hi Miguel,

I was able to declare the variables without issue but when I try and add them into my expression I am receiving errors. I am not sure if I am adding them correctly though. When I added them to my expression all that showed was :max(week(today())). I was adding them as master items. Do the variable need to be loaded again in my script before I can use them?

Thanks,

Philip Radovich

[migueldelval]

Hi Philip,

Normaly it´s useful to declare our variables after main variables in script editor.

And to use it in a expression to need to put like: $(vEndQ)

Try it and tell me if you want.

Regards

Miguel del Valle