topic Re: KPis Calculation in QlikView

KPis Calculation

mario-sarkis — Wed, 25 Nov 2020 16:16:04 GMT

Hey all ,

Here is my case :

I have a list of Accounts that has loans i need to calculate the following

(Count ID that has BUCKET >= 2 This month) / (Count ID that has BUCKET = 1 last month)

PS: Denominator should only count the IDs that has Bucket 1 last month not all IDs

Example:

Last Month Current Month

ID Bucket ID Bucket

1 1 1 1

2 1 2 2

3 1 3 3

4 4 4 5

In this example the answer will be 2 / 3 = 66,7% since ID 1,2,3 has bucket 1 in last month and from these IDs 2,3 now they greater or equal that 2 bucket.

I need to calculate this in a set analysis.

Thank you,

hope you can Help

Re: KPis Calculation

sunny_talwar — Tue, 27 Dec 2016 12:45:36 GMT

How do you determine Current Month vs Last Month? You have a date or month field? Also, you need this in a text box object? I guess the second part of the question was answered by your heading

Re: KPis Calculation

mario-sarkis — Tue, 27 Dec 2016 12:52:39 GMT

Hey Sunny ,

Thank you for you reply and hope that you can help me with this.

I have a date field not month (num)

i used this to determine the maximum period "[H_CLD PeriodDate]={$(=max({<[CLD Record Type]={'CLD'}>} [H_CLD PeriodDate]))} "

My problem is to identify and COUNT only the IDs that has a Bucket = 1 last month and now they have more than 2 (so i don't want to count all id for this month)

Thank you.

Re: KPis Calculation

sunny_talwar — Tue, 27 Dec 2016 13:00:38 GMT

I think you have already got the denominator part of this already working? right? Would you be able to share your expression for last month which you are using for your denominator part?

Re: KPis Calculation

mario-sarkis — Tue, 27 Dec 2016 13:10:02 GMT

Hey Sunny,

Count (DISTINCT{<Bucket={"1"},[H_CLD PeriodDate]={">=$(=monthstart(addmonths(max({<[CLD Record Type]={'CLD'}>} [H_CLD PeriodDate]),-1)))<=$(=monthend(addmonths(max({<[CLD Record Type]={'CLD'}>} [H_CLD PeriodDate]),-1)))"}>} [CLD Account])

this is the expression that i am using for last month

But as i told you for this month i don't want to count all IDs that has bucket >=2 Only i need to Count the IDs that have bucket =1 last month and now they have more than 2

Re: KPis Calculation

sunny_talwar — Tue, 27 Dec 2016 13:15:47 GMT

Try this:

Count(DISTINCT {<Bucket = {'>1'}, [H_CLD PeriodDate] = {$(=Max({<[CLD Record Type]={'CLD'}>} [H_CLD PeriodDate]))}, [CLD Account] = {"=Count (DISTINCT {<Bucket = {1}, [H_CLD PeriodDate] = {'>=$(=MonthStart(AddMonths(Max({<[CLD Record Type] = {CLD}>} [H_CLD PeriodDate]), -1)))<=$(=MonthEnd(AddMonths(Max({<[CLD Record Type] = {CLD}>} [H_CLD PeriodDate]), -1)))'}>} [CLD Account]) = 1"}>} [CLD Account])

Re: KPis Calculation

dsharmaqv — Tue, 27 Dec 2016 13:16:23 GMT

May be you can try something like this

count({<Bucket={">=2"}>*<Month={'$(vCurrentMonth'}>} ID) / count({<Bucket={">=2"}>*<Month={'$(vLastMonth'}>} ID)

note : vCurrentMonth = GetFieldSelections(Month)

vLastMonth = GetFieldSelections(Month) -1

Re: KPis Calculation

mario-sarkis — Tue, 27 Dec 2016 14:09:46 GMT

Sunny Thank you so much , it works

but i have a Problem :

IF i am not Selecting a month this shows me a right output ,

but the problem is when i select a month it shows me zero .

Noting that Data of each month is available in end of month (some months 27/11/2016 and others for example 26/11/2016)..

How can solve this in the Expression that you provide it to me since it is correct?

Re: KPis Calculation

sunny_talwar — Tue, 27 Dec 2016 14:25:04 GMT

I guess you need to ignore selection in Month field may be where you are making your selections?

Re: KPis Calculation

mario-sarkis — Tue, 27 Dec 2016 14:28:00 GMT

But let's say i want to the ratio in the previous month or before ,i have to select to check , if i ignore the selection on month the output will nor change ?

Re: KPis Calculation

sunny_talwar — Tue, 27 Dec 2016 14:34:42 GMT

I see what you are saying, but I will have to play around with this to give you an expression which can work for you. Are you able to share a sample qvw? Because without one, it is going to be very very difficult and time consuming for me to get what you want

Re: KPis Calculation

mario-sarkis — Tue, 27 Dec 2016 14:39:22 GMT

Thanks Bro it work :

i only added 1 to ignore the selections as u advised .

Count(DISTINCT {<Bucket = {'>1'}, [H_CLD PeriodDate] = {$(=Max({<[CLD Record Type]={'CLD'}>} [H_CLD PeriodDate]))}, [CLD Account] = {"=Count (DISTINCT { 1 <Bucket = {1}, [H_CLD PeriodDate] = {'>=$(=MonthStart(AddMonths(Max({<[CLD Record Type] = {CLD}>} [H_CLD PeriodDate]), -1)))<=$(=MonthEnd(AddMonths(Max({<[CLD Record Type] = {CLD}>} [H_CLD PeriodDate]), -1)))'}>} [CLD Account]) = 1"}>} [CLD Account])

Thank you

Re: KPis Calculation

sunny_talwar — Tue, 27 Dec 2016 14:48:15 GMT

Super