Rangesum() in running total Help.

[aniketsr]

Hi Team,

Iam using rangesum and above function and plotting running total against (ID,Year,month).

Consider we have Year as 2015 and 2016.

If we select both the years the running total against the month is coming perfect,

But once i select 2016 the running total get affected as the rows get reduced for 2016.

i'am using the below expression :

=RangeSum(Above(TOTAL Sum({<MONTH=,YEAR=>}Amount),0,12))

Can someone guide me for an expression which should give static result against the respective month even after we select 2016.

Thank You.

Scripting‌

QlikView App Development‌

New to QlikView‌

[aniketsr]

Hi Sunny,

Thanks for the Guidance, the numbers  are static even if i select 2016 .

but the numbers change if we select only month .

attached is the screenshot for the scenario

[aniketsr]

Hey Hi Sunny,

It got resolved i had just to Exclude the month in set expression.

Thanks for your support

[sunny_talwar]

That is awesome

Please close this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny

[aniketsr]

Hi Sunny,

I tried the code given by you it is working perfect in QV 12.

As you stated that in QV 11.2 i did the sorting in the script but then too the numbers are not populating correct for rolling 12.

any expert advise on this.

[sunny_talwar]

Can you share the script you have used to fix the sorting?

[aniketsr]

I just took the resident of the table where we have IntYearMonth and wrote Order by IntYearMonth

[sunny_talwar]

That is not going to help because Load order is determined by the first time a field is loaded... since you pre-resident load the sort was not what we wanted... this is not going to work... You can try this

LOAD DISTINCT IntYearMonth,

           RowNo() as Sort

Resident ....

Order By....;

now instead of using IntYearMonth in your Aggr() dimension, use Sort. New dimension sort is sorted in the way you want when it got created the first time.

Not saying this is the best way to do this, but with the information you are providing, this should work.

Best,

Sunny

[aniketsr]

I Tried doing the above step now the rolling 12 month column turns blank when i aggregate the expression by Sort