topic Re: Sum Max in QlikView

Sum Max

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

I have the following expression and I want to only see the Max New_Reporting_Period but for some reason I can't get my head round it

=count(distinct If(DaysToTS_Acc<21,Risk_No))

I'm sure it's simple!

Re: Sum Max

sunny_talwar — Thu, 13 Jul 2017 13:48:27 GMT

May be this

FirstSortedValue(Aggr(Count(DISTINCT {<DaysToTS_Acc = {'<21'}>} Risk_No), New_Reporting_Period), -New_Reporting_Period)

Re: Sum Max

sunny_talwar — Thu, 13 Jul 2017 13:48:58 GMT

This should show the count for the Max New_Reporting_Period

Re: Sum Max

Kushal_Chawda — Thu, 13 Jul 2017 13:49:21 GMT

What is the format of New_Reporting_Period field?

Re: Sum Max

kevbrown — Thu, 13 Jul 2017 13:52:10 GMT

Awesome, thanks Sunny, and to do the previous month?

Re: Sum Max

sunny_talwar — Thu, 13 Jul 2017 13:53:03 GMT

This

FirstSortedValue(Aggr(Count(DISTINCT {<DaysToTS_Acc = {'<21'}>} Risk_No), New_Reporting_Period), -New_Reporting_Period, 2)

Re: Sum Max

sunny_talwar — Thu, 13 Jul 2017 13:54:21 GMT

Actually these might also work:

Max

Count(DISTINCT {<DaysToTS_Acc = {'<21'}, New_Reporting_Period = {"$(=Max(New_Reporting_Period))"}>} Risk_No)

2nd Max

Count(DISTINCT {<DaysToTS_Acc = {'<21'}, New_Reporting_Period = {"$(=Max(New_Reporting_Period, 2))"}>} Risk_No)

Re: Sum Max

kevbrown — Thu, 13 Jul 2017 13:59:52 GMT

Yes, these worked perfectly. Thank you

Re: Sum Max

kevbrown — Thu, 13 Jul 2017 14:05:43 GMT

Last question on this, can this be adapted to be a rolling 3 month? so included the Max 3 months?

Re: Sum Max

sunny_talwar — Thu, 13 Jul 2017 15:03:12 GMT

May be this

Count(DISTINCT {<DaysToTS_Acc = {'<21'}, New_Reporting_Period = {"$(='>=' & Max(New_Reporting_Period, 3) & '<=' & Max(New_Reporting_Period))"}>} Risk_No)

Re: Sum Max

kevbrown — Thu, 13 Jul 2017 15:09:16 GMT

Thank you