Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
shreya_nadkarni
Partner - Creator
Partner - Creator

Aggr(RangeSum(Above( function not working

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.

Test3.pngloveisfailstalwar1‌@

1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

11 Replies
shreya_nadkarni
Partner - Creator
Partner - Creator
Author

jagan

sunny_talwar

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

shreya_nadkarni
Partner - Creator
Partner - Creator
Author

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 .

Test4.png

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.

Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
sunny_talwar

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)

shreya_nadkarni
Partner - Creator
Partner - Creator
Author

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

loveisfailstalwar1


Anil_Babu_Samineni

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

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
shreya_nadkarni
Partner - Creator
Partner - Creator
Author

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.

sunny_talwar

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