Calculate variance between 2 dates in Qlik Sense

[Kaushik2020]

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.

[rubenmarin]

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.

[Kaushik2020]

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. 

[rubenmarin]

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

SET vFromDate = =Min(DateField);

SET vToDate = =Max(DateField);