topic Re: Count over base not giving right calculated values in QlikView

Count over base not giving right calculated values

alfredho — Wed, 25 Nov 2020 16:16:04 GMT

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)))

Re: Count over base not giving right calculated values

ashok_rajm — Fri, 05 Apr 2019 10:20:04 GMT

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

Re: Count over base not giving right calculated values

alfredho — Fri, 05 Apr 2019 13:51:31 GMT

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?

Re: Count over base not giving right calculated values

alfredho — Mon, 08 Apr 2019 00:51:07 GMT

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])

Re: Count over base not giving right calculated values

alfredho — Mon, 08 Apr 2019 08:37:30 GMT

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?

Re: Count over base not giving right calculated values

ashok_rajm — Tue, 09 Apr 2019 10:36:47 GMT

Can you share the sample application.