KPis Calculation

[mario-sarkis]

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

[sunny_talwar]

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

[mario-sarkis]

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

[sunny_talwar]

Super