topic Re: Calculate variance between 2 dates in Qlik Sense in App Development

Calculate variance between 2 dates in Qlik Sense

Kaushik2020 — Sun, 18 May 2025 06:19:59 GMT

Dear All,

I have a scenario where i have 2 date picker in UI to select from_date and To_Date.

below is my expression where i have to pass these 2 dates. can anyone suggest on how this can be done using a variable ?

Num ((Avg({$<Year=, Month=, Day=, CustomKPILabel={'1_Percentage'},
ReportDate={">=$(=date(monthstart(max(ReportDate))))<=$(=date(max(ReportDate)))"}>}
Value)
/
Avg({$<Year=, Month=, Day=, CustomKPILabel={'1_Percentage'},
ReportDate={">=$(=date(monthstart(max(ReportDate),-12)))<=$(=date(addmonths(max(ReportDate),-12)))"}>}
Value))-1,
CHR(9650)&'#,##0.0%;'&CHR(9660)&'#,##0%')

Blue --> From_Date

Red--> To_Date

thanks in advance.

Re: Calculate variance between 2 dates in Qlik Sense

rubenmarin — Sun, 18 May 2025 06:49:18 GMT

Hi, with $(variable Name) you can retrieve the variable value, ie:

Num ((Avg({$<Year=, Month=, Day=, CustomKPILabel={'1_Percentage'},
ReportDate={">=$(=date(monthstart('$(From_Date)')))<=$(=date('$(From_Date)'))"}>}
Value)
/
Avg({$<Year=, Month=, Day=, CustomKPILabel={'1_Percentage'},
ReportDate={">=$(=date(monthstart('$(To_Date)',-12)))<=$(=date(addmonths('$(To_Date)',-12)))"}>}
Value))-1,
CHR(9650)&'#,##0.0%;'&CHR(9660)&'#,##0%')

Maybe you also need to use Date# depending of the date format set on the variables.

But if you are using the default date picker object, it uses a field, not a variable, so you only need to change your original expression to the field names used in teh date picker.

Re: Calculate variance between 2 dates in Qlik Sense

Kaushik2020 — Sun, 18 May 2025 07:07:01 GMT

thanks for the swift reply. How we can pass the Dates selected by the user into the variables.

Eg:

I have below variables created in my load script to store FromDate and ToDate..

vFromDate=

vToDate=

thanks in Advance.

Re: Calculate variance between 2 dates in Qlik Sense

rubenmarin — Mon, 19 May 2025 06:07:48 GMT

Hi, if you define a variable that starts with an equal sign, it is evaluated, ie:

SET vFromDate = =Min(DateField);

SET vToDate = =Max(DateField);