Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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)))
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)))
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.
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))
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)
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
Better if you could share the sample. We Will think little Sort option using Order By in Script level.
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.
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