topic Re: Aggr(RangeSum(Above( function not working in QlikView

Aggr(RangeSum(Above( function not working

shreya_nadkarni — Wed, 25 Nov 2020 16:16:04 GMT

Hello Everyone.

Need help in writing the expression for Bar chart.

The req is for date 07/09/2018 for the BU need to Minus the Above value and so on.

Expected Output -07/09/2018 for BU(IBG1) ----value should be 21-12.

-07/09/2018 for BU(IBG2)----value should be 3-2

Thanks for the helps.

loveisfail‌stalwar1‌@

Re: Aggr(RangeSum(Above( function not working

shreya_nadkarni — Wed, 17 Oct 2018 09:09:11 GMT

jagan‌

Re: Aggr(RangeSum(Above( function not working

sunny_talwar — Wed, 17 Oct 2018 10:21:45 GMT

Try this

Aggr(Sum(Value) - Above(Sum(Value)), BU, SNAPSHOT_DATE)

the above will work as long as your SNAPSHOT_DATE is ordered in ascending order in the script. If it does not and you happen to be on QV12 or above, then you can try this as an alternative

Aggr(Sum(Value) - Above(Sum(Value)), BU, (SNAPSHOT_DATE, (NUMERIC)))

Re: Aggr(RangeSum(Above( function not working

shreya_nadkarni — Wed, 17 Oct 2018 10:51:18 GMT

Hi Sunny ,

Please find the detailed description of my requirement below:

1. Attached is the straight table with 2 dimensions in the order Date , Business unit and my expression is count(Field).In the sort tab of the chart prop, i have sorted by Business unit first then by date .

As you can see i am finding out the difference using above function.

I want the same replicated the same in a line chart , the problem being that i am unable to Sort on Business unit first . I want to plot Date on x axis and add business Unit as my second dimension.

Re: Aggr(RangeSum(Above( function not working

Anil_Babu_Samineni — Wed, 17 Oct 2018 13:34:28 GMT

Which version Of QlikView you are using? Sunny's second suggestion works as expected if you are using advanced version. Try this? Or please share sample file

Aggr(Sum(Value) - Above(Sum(Value)), BU, (SNAPSHOT_DATE, (NUMERIC), DESC))

Re: Aggr(RangeSum(Above( function not working

sunny_talwar — Wed, 17 Oct 2018 13:41:39 GMT

Which version of QlikView are you using? Did you try to use this?

Aggr(Sum(Value) - Above(Sum(Value)), BU, (SNAPSHOT_DATE, (NUMERIC)))

or this

Aggr(Sum(Value) - Above(Sum(Value)), BU, SNAPSHOT_DATE)

Re: Aggr(RangeSum(Above( function not working

shreya_nadkarni — Thu, 18 Oct 2018 03:31:33 GMT

Hi Sunny/Anil

I tried both the expressions.

Aggr(Sum(Value) - Above(Sum(Value)), BU, (SNAPSHOT_DATE, (NUMERIC))) works for version 12.20... due to the NUMERIC keyword the sorting of BU,Snapshot is automatically handled in the sort properties.

however mine is 11 version qlik.

Aggr(Sum(Value) - Above(Sum(Value)), BU, SNAPSHOT_DATE) expression is not giving correct output.

can you please give me an alternative to use Aggr(Sum(Value) - Above(Sum(Value)), BU, SNAPSHOT_DATE)

and include NUMERIC functionality.

Thanks

loveisfail‌stalwar1‌

Re: Aggr(RangeSum(Above( function not working

Anil_Babu_Samineni — Thu, 18 Oct 2018 03:34:59 GMT

Better if you could share the sample. We Will think little Sort option using Order By in Script level.

Re: Aggr(RangeSum(Above( function not working

shreya_nadkarni — Thu, 18 Oct 2018 09:48:21 GMT

Hi,

Attached the app. Expression is working with this sample data with or without NUMERIC.

But the original app is not working without NUMERIC although i have tried to recreate the scenario.

Since i am using qlik 11 version.

Thanks.

Re: Aggr(RangeSum(Above( function not working

sunny_talwar — Thu, 18 Oct 2018 10:38:10 GMT

All you need to do.... for your original app is to sort the data by SNAPSHOT_DATE..... like this

Test:

LOAD SNAPSHOT_DATE as Temp,

BUSINESS_UNIT,

QUOTATION_REF

FROM

Sample.xlsx

(ooxml, embedded labels, table is Sample);

FinalTable:

LOAD Temp as SNAPSHOT_DATE,

BUSINESS_UNIT,

QUOTATION_REF

Resident Test

Order By SNAPSHOT_DATE;

DROP Table Test;

If for some reason you don't want to do this... you can also do something like this

Test:

LOAD SNAPSHOT_DATE,

BUSINESS_UNIT,

QUOTATION_REF

FROM

Sample.xlsx

(ooxml, embedded labels, table is Sample);

SortTable:

LOAD Distinct SNAPSHOT_DATE,

SNAPSHOT_DATE as SNAPSHOT_DATE_SORT

Resident Test

Order By SNAPSHOT_DATE;

Here I am creating a new field called SNAPSHOT_DATE_SORT which is sorted in the correct order and can be used in place of SNAPSHOT_DATE in your aggr() function because it is a one to one match between the two field... so the new expression will look like this

Aggr(Sum(Value) - Above(Sum(Value)), BU, SNAPSHOT_DATE_SORT)

I hope this helps.

Best,

Sunny

Re: Aggr(RangeSum(Above( function not working

shreya_nadkarni — Fri, 19 Oct 2018 03:23:32 GMT

Thanks a lot stalwar1‌ loveisfail‌.

This was indeed helpful.

Re: Aggr(RangeSum(Above( function not working

shreya_nadkarni — Fri, 19 Oct 2018 09:15:40 GMT

Hi Sunny,

Only after using a mapping table SortTable where i sorted in asc in script did it work.

Also i had to use the new field SNAPSHOT_DATE_SORT instead of the original SNAPSHOT_DATE in my expression then the chart gave me the proper result.

Thanks a lot.