Give me the AR day number when cash hits 99%

[ugurarslan]

Dear.

I have created a pivot table with the following data:

Row: [AR day nbr] - gives me the day number of AR 

Collumn: [Inv Year Month]

Measures: 

=rangesum(above(Sum({<[Type]={'APP'},[Cash App Type]={'CASH'}>}[AR USD]*(-1))
/
sum({<[Type]={'INV'},[Rebill Indicator]={'Non Rebill'}>}total<[AR Year Month]>[AR USD]),0,RowNo()))

Result is:

AR  ay Nbr	Inv  Month	Feb 2017
-13		3.0%
-12		4.3%
-11		5.3%
-10		6.4%
-9		6.8%
-8		8.2%
-7		8.5%
-6		13.3%
-5		17.3%
-4		20.4%
-3		24.3%
-2		28.0%
-1		28.4%
0		63.0%
1		64.6%
2		77.2%
3		81.8%
4		84.4%
5		85.6%
74		98.6%
75		98.6%
76		98.6%
89		98.6%
90		98.7%
91		98.7%
93		98.7%
94		98.7%
95		98.8%
96		98.7%
97		98.7%
98		98.7%
99		98.7%
111		98.9%
112		98.9%
113		98.9%
114		99.0%
115		99.0%
116		99.0%
125		99.1%

Now I want to create a KPI where I can show in the KPI what the the day number is when hit the number >99%. For an example, in the example used above the KPI should show me 114.

Can you please help me?

Thanks a lot.

Ugur

[ugurarslan]

Dear.

I have created a pivot table with the following data:

Row: [AR day nbr] - gives me the day number of AR 

Collumn: [Inv Year Month]

Measures: 

=rangesum(above(Sum({<[Type]={'APP'},[Cash App Type]={'CASH'}>}[AR USD]*(-1))
/
sum({<[Type]={'INV'},[Rebill Indicator]={'Non Rebill'}>}total<[AR Year Month]>[AR USD]),0,RowNo()))

Result is:

AR  ay Nbr	Inv  Month	Feb 2017
-13		3.0%
-12		4.3%
-11		5.3%
-10		6.4%
-9		6.8%
-8		8.2%
-7		8.5%
-6		13.3%
-5		17.3%
-4		20.4%
-3		24.3%
-2		28.0%
-1		28.4%
0		63.0%
1		64.6%
2		77.2%
3		81.8%
4		84.4%
5		85.6%
74		98.6%
75		98.6%
76		98.6%
89		98.6%
90		98.7%
91		98.7%
93		98.7%
94		98.7%
95		98.8%
96		98.7%
97		98.7%
98		98.7%
99		98.7%
111		98.9%
112		98.9%
113		98.9%
114		99.0%
115		99.0%
116		99.0%
125		99.1%

Now I want to create a KPI where I can show in the KPI what the the day number is when hit the number >99%. For an example, in the example used above the KPI should show me 114.

Can you please help me?

Thanks a lot.

Ugur

[Wlad_Masi]

Hello there,

I worked with the sample data you provided me, but I am not sure if it will escale for your solution. Maybe you will have to use a variable in your script to scale it up.
Here is what I did in order to test it.

Script:

Comm:
Load * inline [

AR_Nbr, Inv_ Month, Feb-17
-13, ,3.00%
-12, ,4.30%
-11, ,5.30%
-10, ,6.40%
-9, ,6.80%
-8, ,8.20%
-7, ,8.50%
-6, ,13.30%
-5, ,17.30%
-4, ,20.40%
-3, ,24.30%
-2, ,28.00%
-1, ,28.40%
0, ,63.00%
1, ,64.60%
2, ,77.20%
3, ,81.80%
4, ,84.40%
5, ,85.60%
74, ,98.60%
75, ,98.60%
76, ,98.60%
89, ,98.60%
90, ,98.70%
91, ,98.70%
93, ,98.70%
94, ,98.70%
95, ,98.80%
96, ,98.70%
97, ,98.70%
98, ,98.70%
99, ,98.70%
111, ,98.90%
112, ,98.90%
113, ,98.90%
114, ,99.00%
115, ,99.00%
116, ,99.00%
125, ,99.10%
];

Comm2:
load
AR_Nbr,
[Feb-17],
if([Feb-17] >= '99.00%', [Feb-17] ) as Flag
resident Comm;

Drop Table Comm;

Visualization:

Hopefully it will help you.