topic Re: Resetting Running Total Field value based on other fields in QlikView

Resetting Running Total Field value based on other fields

rahulpawarb — Wed, 08 Feb 2017 12:49:00 GMT

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

Re: Resetting Running Total Field value based on other fields

adamdavi3s — Wed, 08 Feb 2017 13:47:40 GMT

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)

Re: Resetting Running Total Field value based on other fields

sunny_talwar — Wed, 08 Feb 2017 14:44:19 GMT

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$);

Re: Resetting Running Total Field value based on other fields

adamdavi3s — Wed, 08 Feb 2017 14:46:37 GMT

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

Re: Resetting Running Total Field value based on other fields

sunny_talwar — Wed, 08 Feb 2017 14:48:29 GMT

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

Re: Resetting Running Total Field value based on other fields

sunny_talwar — Wed, 08 Feb 2017 14:49:17 GMT

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

Re: Resetting Running Total Field value based on other fields

rahulpawarb — Thu, 09 Feb 2017 05:54:52 GMT

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

Re: Resetting Running Total Field value based on other fields

sunny_talwar — Thu, 09 Feb 2017 10:23:41 GMT

May be add all your dimension in the key

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

Re: Resetting Running Total Field value based on other fields

rahulpawarb — Thu, 09 Feb 2017 12:04:01 GMT

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

Re: Resetting Running Total Field value based on other fields

sunny_talwar — Thu, 09 Feb 2017 12:06:26 GMT

For that look at this thread:

Continuous Ranking (no missing Rank)

Re: Resetting Running Total Field value based on other fields

rahulpawarb — Thu, 09 Feb 2017 12:11:51 GMT

Thank you I will have a look at it.

Regards!

Rahul