Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
rahulpawarb
Specialist III
Specialist III

Resetting Running Total Field value based on other fields

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:

RunningTotal.PNG

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

Looking forward to your affirmative response.

Regards!

Rahul

1 Solution
10 Replies
adamdavi3s
Master
Master

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
Master
Master

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
Specialist III
Specialist III
Author

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
Specialist III
Specialist III
Author

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.

Issue.PNG

Regards!

Rahul