Resetting Running Total Field value based on other fields

[rahulpawarb]

Hello Friends,

Hope you are doing great!

In below screenshot, I have Rank field, which needs to be corrected. Currently it is showing RowNo for each record. I want it to populate it based on below condition.

- It should start with 1 for each FGrp#, increment by one for each Sales value (Sales in descending order i.e. Highest sales in given region will listed first in the list)

- If there are same Sales values in given FGrp# then same Rank values needs to be assigned to both the records.

Present Situation:

Please refer attached application, sample data and expected result sheet for your reference.

Looking forward to your affirmative response.

Regards!

Rahul

[adamdavi3s]

There must be a way to rank it but I can't seem to get the syntax right for some reason

aggr(rank( Sum( Sales)),FGrp#,NHBCust)

[sunny_talwar]

Try this:

Aggr(Rank(Sum(Sales), 1), FGrp#, Key)

Where Key is created in the script like this:

SampleData:

LOAD Rgn,

    RepName,

    FGrp#,

    SDWACT#,

    NHBCust,

    SysCust,

    Ship,

    CustName,

    City,

    State,

    Sales,

    AutoNumber(Ship&City&State&SysCust) as Key

FROM

SalesData.xls

(biff, embedded labels, table is Sheet1$);

[adamdavi3s]

Gah I tried that (using a hidden calculated dimension) but my issue was I added FGrp# in the key as well, so dumb!

[sunny_talwar]

Adding FGrp# to the key should not be a problem. In fact I think its a great idea.... Let me test it out and get back

[sunny_talwar]

I think it works still... check out the attachement

[rahulpawarb]

Many Many thanks Sunny and Adam for becoming my saviors.

Solution provided by you worked well for single table model. However, It is killing me with dimensional model (I am implementing it with trial & error method). Attached is the sample application file.

Looking forward to hear from you.

Regards!

Rahul

[sunny_talwar]

May be add all your dimension in the key

AutoNumber(REGION&SALREP&DNAME&FINGRP&SYSCST&CMNAME&CUSTNO&CITY&STATE) as Key

[rahulpawarb]

Thank you Sunny. I will try it out.

However, while reviewing the earlier solution I found that Rank field is resetting the values for every FGrp# and if the Sales is same then it is retaining the same value; but while doing this it skips the intermediate value. In below given screenshot it is showing Rank values as 1, 4 & 6 where as it should show 1, 2 & 3.

Regards!

Rahul

[sunny_talwar · Accepted solution]

For that look at this thread:

Continuous Ranking (no missing Rank)