Count

[Nikhil2725]

Hi,

I am calculating the Cumulative % in one of the table by using below measure:

Num(round(rangesum(above(total sum(GrossValue_LC),0,RowNo(total))))/
round(sum(total Aggr(sum(GrossValue_LC),PH6)))*100,'##.00')

If i use the above measure ill get the output like 7.48,80.55,65.36,20.05,27.10,83.55 and so on....

Now my requirement is like i want to count how many is greater than 80.00 but in the new table..

In the above example which i have given i want the output like 2..

Can i do it???

[Kashyap_R]

Hi 

try creating the cumulative in backend/script as a separate column and use that in your calculation.

https://community.qlik.com/t5/QlikView-Scripting/Cumulative-sum-in-script/td-p/1360944

Hope this helps

Thanks

[sunny_talwar]

What was your sort order in the chart where you used this? Also, what all dimensions did you have in your chart? This is what you would need to know to do this on the front end itself (if you want to do it this way)

[Nikhil2725]

Hi Sunny, I have attached the File which I have created.

I need to create one more table where i need the only a count > than 80% of the Cumulative T/O %.

[Nikhil2725]

Hi Kashyap,

I tried the cumulative Sum in the Script. However I am getting Invalid expression.

cumulative:
Load
PH6,GrossValue_LC,RowNo(),
RangeSum(peek(Sum(GrossValue_LC),0,RowNo())) as Cumulative_1
Resident Data_temp;
drop Table Data_temp;

[Kashyap_R]

Hi

try this instead for range sum

Sum(GrossValue_LC)+Peek(Cum_GrossValue_LC) as Cum_GrossValue_LC 

Hope this helps

Thanks

[Nikhil2725]

Hi,

Its not working..

My script is as below:

Data_temp:
LOAD PH9,
"PH9_desc",
PH6,
"PH6_desc",
PH3,
"PH3_desc",
PF3,
"Div",
"Div_desc" ;
SQL SELECT PH9,
"PH9_desc",
PH6,
"PH6_desc",
PH3,
"PH3_desc",
PF3,
"Div",
"Div_desc"
FROM PH9;

left join Data_temp:
LOAD MaterialNr,
"Material_Desc",
PH9,
"Exclude" ;
SQL SELECT MaterialNr,
"Material_Desc",
PH9,
"Exclude"
FROM Material;

left join Data_temp:
LOAD InvoiceNr,
InvoiceLine,
OrderNr,
OrderLine,
BillingDate,
BillingYear,
MaterialNr,
"GrossValue_LC",RowNo();
SQL SELECT InvoiceNr,
InvoiceLine,
OrderNr,
OrderLine,
BillingDate,
BillingYear,MaterialNr,
"GrossValue_LC"

FROM  Invoice where BillingYear='2012';

cumulative:

Load
PH6,GrossValue_LC,RowNo(),
Sum(GrossValue_LC)+Peek(Cum_GrossValue_LC) as Cum_GrossValue_LC
Resident Data_temp
Order by PH6;
drop Table Data_temp;

[sunny_talwar · Accepted solution]

You need this in a KPI object? Try this

Sum(Aggr(
	If(Round(RangeSum(Above(TOTAL Sum({<[PH6] = {"=Sum(GrossValue_LC) > 0"}>} GrossValue_LC), 0, RowNo(TOTAL))))/
	Round(Sum(TOTAL {<[PH6] = {"=Sum(GrossValue_LC) > 0"}>} GrossValue_LC)) < 0.80, 1, 0),
([PH6], (=Sum(GrossValue_LC), DESC))))

[Nikhil2725]

Hi Sunny,

Thanks for the solution you provided. Its working..

Cheers...