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: 
alfredho
Contributor
Contributor

Count over base not giving right calculated values

Hi guys, 

I have the following syntax, to show current and past 2 months trending. The part highlighted in red is not giving me the right calculation. However, when i input a specific month (in green) the result is correct. I need expert advise. Thank you

If([Data Value] = 'CSFA',

Num(Sum({<Reporting ={">=$(=AddMonths(Max([Reporting]),-2))<=$(=Max([Reporting])) "}>} Count)/ 612, '##.00%'),

If([Data Value] = 'CDD',

Num(Sum({<[Reporting] ={">=$(=AddMonths(Max([Reporting]),-2))<=$(=Max([Reporting]))"}>} Count) /
Sum({<[CDDMonth] ={">=$(=AddMonths(Max([CDDMonth]),-2))<=$(=Max([CDDMonth]))"},
[Day Bucket] = {'Total Overdue'}>} [CDD cases]), '##.00000000'),

Num(Sum({<Reporting ={"28/02/2019"}, [Data Value] = {"CDD"}>} Count) /
Sum({<CDDMonth ={"28/02/2019"},
[Day Bucket] = {"Total Overdue"}>} [CDD cases]), '##.00000000')


Sum({<Reporting ={">=$(=AddMonths(Max([Reporting]),-2))<=$(=Max([Reporting]))"}>} Count)))

5 Replies
ashok_rajm
Contributor III
Contributor III

Hi,
Check your date format and Calculate Start and end date in Variable and use those variable in Set analysis, But make sure Field Value format and Variable Value format are same -
Sum({<Reporting={'>= $(vStart_Date) <= $(vEnd_Date)'}>} Count)

Hope this helps
alfredho
Contributor
Contributor
Author

Doesn't seems to work. Am currently working on 2 tables. One which I derive the counts, while the other provide the base, however the latter has fewer months. But the date formats are the same, will that be an issue? 

alfredho
Contributor
Contributor
Author

1.jpg

Hi, i do not know why my syntax is giving me these duplicate totals.  

Sum({<[CDDMonth] ={">=$(=AddMonths(Max([CDDMonth]),-2))<=$(=Max([CDDMonth]))"},
[Day Bucket] = {'Total Overdue'}>} [CDD cases])

alfredho
Contributor
Contributor
Author

Hi, the earlier issue is due to the joining tables issues. 

I have a new issue. Am trying to sum by month and the below syntax has instead given me total of 3 months.

Sum({<[Reporting] = {">=$(=AddMonths(Max([Month]),-2))<=$(=Max([Month]))"},
[Day Bucket] = {'Total Overdue'}>} [Cases])

How to resolve this?

ashok_rajm
Contributor III
Contributor III

Can you share the sample application.