Count IF

[Nikhil2725]

Hi All,

I have measure like:

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

Here I will get the Output in Percentage like(7.47%,14.49%,20.14%,80.56%,85.66%,87.99%)

Now my question is like I want count of percentage greater than 80.00% how can i Do it???..

Kindly Help Me...

[Nikhil2725]

I have around 3 dimension and 6 Measures.

Please find the attached image

[sunny_talwar]

So, the three dimension you have are these?

1) PH3
2) PH3_desc
3) Division

[Nikhil2725]

Yes

[sunny_talwar]

May be this

Sum(Aggr(

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

, PH3, PH3_desc, Division))

This might still not work... because you might need to figure out the sorting.... Are you sorting this based on the descending over of Sales T/O? If you are, then you might need to create a new field in the script which combines PH3, PH3_desc and Division into a single field.

LOAD PH3,
     PH3_desc,
     Division,
     PH3&'|'PH3_desc&'|'&Division as Key,
     ...
From/Resident ...;

And then this

Sum(Aggr(

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

, (Key, (=Sum(GrossValue_LC), desc))))

[Nikhil2725]

Yes sunny,

Im sorting this based on the descending over of Sales T/O... In the main table...

Can i use your function in new table so that ill get only count??

[sunny_talwar]

Sure

[Nikhil2725]

 Hi sunny,

If i use cumalitive Sum cant we able to do sorting ???

for the cumulative column I used the below:

Aggr(rangesum(above(total sum(GrossValue_LC),0,RowNo())),(sum(GrossValue_LC),(Numeric)))

I want to sort sum(GrossValue_LC )in descending order.. Im not able to do it

[sunny_talwar]

What dimension are you sorting the Sum(GrossValue_LC) value by? You have not listed the field... also, you are missing an equal sign

Aggr(
    Rangesum(Above(TOTAL Sum(GrossValue_LC), 0, RowNo()))
, (SortField, (=Sum(GrossValue_LC), Numeric)))

[Nikhil2725]

Hi Sunny,

Sum(GrossValue_LC) this is measure.. From which I used to retrieve cumulative value...

[sunny_talwar]

okay great