Number formatting not working

[kaushi2020]

I am calculating MTD and LMTD using below expressions: 

MTD:- 

Num(Avg(Aggr(((count({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>}
distinct Txt_IssueID))

/

((Count({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>}
Distinct LAST_MODIFIED_BY)))),Day)),'#0') -- results 5

LMTD:-

Num(Avg(Aggr(((count({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date),-1)))<=$(=date(addmonths(max(Date),-1)))"}>}
distinct Txt_IssueID))

/

((Count({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date),-1)))<=$(=date(addmonths(max(Date),-1)))"}>}
Distinct LAST_MODIFIED_BY)))),Day)),'#0') --results 5 

When I am calculating variance for these 2 numbers with below expression i am getting -6% instead of 0.

NUM ((Num(Avg(Aggr(((count({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>}
distinct Txt_IssueID))

/

((Count({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>}
Distinct LAST_MODIFIED_BY)))),Day)),'#0'))

/

(Num(Avg(Aggr(((count({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date),-1)))<=$(=date(addmonths(max(Date),-1)))"}>}
distinct Txt_IssueID))

/

((Count({$<Year=, Month=, Day=,
Date={">=$(=date(monthstart(max(Date),-1)))<=$(=date(addmonths(max(Date),-1)))"}>}
Distinct LAST_MODIFIED_BY)))),Day)),'#0'))-1,
CHR(9650)&'#,##0%;'&CHR(9660)&'#,##0%')

//Tried using number formatting as Auto and Measure expression.

Expectation is 0.

Please suggest.

[Chirantha · Accepted solution]

 Hello Kaushi,

When you use the Num() function with a number format like '#0', Qlik Sense rounds the number to the nearest integer before performing any further calculations. This could be causing the unexpected -6% result in your variance calculation, even though the MTD and LMTD values appear to be the same.

One possible solution could be to move the Num() function outside of your set analysis expressions and apply it only after you’ve calculated the variance. Here’s an example:

Variance:-
NUM (
   (
       Avg(Aggr(((count({$<Year=, Month=, Day=, Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>} distinct Txt_IssueID)) / (Count({$<Year=, Month=, Day=, Date={">=$(=date(monthstart(max(Date))))<=$(=date(max(Date)))"}>} Distinct LAST_MODIFIED_BY)))),Day))
       /
       Avg(Aggr(((count({$<Year=, Month=, Day=, Date={">=$(=date(monthstart(max(Date),-1)))<=$(=date(addmonths(max(Date),-1)))"}>} distinct Txt_IssueID)) / (Count({$<Year=, Month=, Day=, Date={">=$(=date(monthstart(max(Date),-1)))<=$(=date(addmonths(max(Date),-1)))"}>} Distinct LAST_MODIFIED_BY)))),Day))
   ) - 1,
   CHR(9650)&'#,##0%;'&CHR(9660)&'#,##0%'
)