Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
ugurarslan
Contributor

Give me the AR day number when cash hits 99%

 
 

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

Labels (5)
1 Reply
Support
Support

Re: Give me the AR day number when cash hits 99%

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:

Min(flag).png

 

Hopefully it will help you. 


To help users find verified answers, please don't forget to mark a correct resolution or answer to your problem or question as correct.