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]

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

[mario-sarkis]

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.

[sunny_talwar]

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?

[mario-sarkis]

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

[sunny_talwar · Accepted solution]

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

[dsharmaqv]

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

[mario-sarkis]

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?

[sunny_talwar]

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

[mario-sarkis]

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 ?