Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
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.