topic Creating average from "above" created expression in a chart in QlikView

Creating average from "above" created expression in a chart

Wed, 30 Mar 2016 15:53:20 GMT

Hi All,

I was trying to build this for 4 hours and I failed miserably. Basically what I am trying to do is:

Imagine that you have set of time constraints (that can vary - but I can easily fix that). For the sake of the example, let them be weeks - that are the dimension of the chart = week1, week 2 etc.I am creating the chart from a dataset that looks like below:

Week number Volume Done

1 100

2 123

3 93

4 97

What also I did is I created a line (the chart was bar + line, when bar shows the volume in total) that shows the movement in the volume shown in percentage - for example - in week 2 we have 23 % increase compared to week 1 and in week 3 we have around 30 % decrease compared tpo week 2) etc.

I used similar expression in the chart = sum(Volume)/above(sum(volume)) -1 and it works fine.

However i am failing to achieve an AVERAGE movement per week. For example if we have -15%, + 5% - 10%, I want to get them all in Positive % and divide them by 3 - simple as that - it should show 10% if we stick to the 3 numbers shown above.

I am trying to show this average percentage either in a text inside the chart or in a textbox outside of the chart. If I am using straight table and if I use the average total it works but it does not show accurate values (when aggr and avg used) inside the chart or in a textbox (same formula used) - it only provides accurate numbers in a straight table (average total).

Please can you help as this is driving me crazy

Re: Creating average from "above" created expression in a chart

oknotsen — Wed, 30 Mar 2016 16:07:32 GMT

Try this:

sum(Volume)/above(sum(volume), -1, 1)

Above ‒ QlikView

Re: Creating average from "above" created expression in a chart

sunny_talwar — Wed, 30 Mar 2016 16:11:16 GMT

What about this:

Avg(fabs(Aggr(Sum(Volume)/Above(Sum(volume)) -1, Week)))

Update: Missed a parenthesis

Re: Creating average from "above" created expression in a chart

sunny_talwar — Wed, 30 Mar 2016 16:16:35 GMT

Sample attached based on the sample data provided

Expression

Avg(fabs(Aggr(Sum(Volume)/Above(Sum(Volume)) -1, Week)))

Re: Creating average from "above" created expression in a chart

Wed, 30 Mar 2016 16:28:00 GMT

Can you implement this in chart or in text object? I mean I tried with aggr and average but in straight table it work - in chart it doesnt' and this is the worst part.

Re: Creating average from "above" created expression in a chart

sunny_talwar — Wed, 30 Mar 2016 16:33:57 GMT

Did it in a text box only

Re: Creating average from "above" created expression in a chart

Wed, 30 Mar 2016 16:51:58 GMT

Perhaps I am doing something wrong here. When i extract the chart data and compare it to the text box avg I get completely different results:

SUM(Alpha)/(above(Sum (Alpha),1))-1 (avg: 1480 % (as we have huuge increase from week 1 Jan to week 2)

=avg(fabs(aggr(SUM(Alpha)/(above(Sum (Alpha),1))-1,WEEK_NBR))) - I get 16 % on average

VOlumes:

26.0

4,231.0

4,477.0

4,600.0

4,682.0

4,901.0

4,575.0

4,688.0

4,657.0

4,696.0

4,582.0

3,435.0

Re: Creating average from "above" created expression in a chart

sunny_talwar — Wed, 30 Mar 2016 17:16:44 GMT

Which data are you using to calculate these percentages? Can you share a sample of where you are working?

Re: Creating average from "above" created expression in a chart

swuehl — Wed, 30 Mar 2016 17:21:50 GMT

Please note that your WEEK_NBR field values need to show a LOAD ORDER in the order you have used in your chart (e.g. just sorted numeric ascending).

If the LOAD ORDER is different from that, create this first in your script;

TMP:

LOAD Recno() as WEEK_NBR

Autogenerate 53; // or how many weeks you have at max

Then load your data.

Finally, you can drop your TMP table, if you want.

edit: Or, if you are using QV12

The sortable Aggr function is finally here!

Re: Creating average from "above" created expression in a chart

Wed, 30 Mar 2016 17:35:59 GMT

In general I have a data pulled from SQL Server - then when I put the data in weekly dimension I have those figures above. You can see that the difference between 26 and 4231 in the first two weeks only is over 1k % increase. The most annoying part is when i put the data in straight table and make an average total it calculates it correctly - but in the chart ot text object it gives me totaly different thing. Once again the volumes weekly are(in positive percentage):

I get it in the chart with:

SUM(Alpha)/(above(Sum (Alpha),1))-1

Weekly view	Volume vs SLA Movement	Volume Movement	positive
1	26,0	-
2	4 231,0	16 173,1%	16 173,1%
3	4 477,0	5,8%	5,8%
4	4 600,0	2,7%	2,7%
5	4 682,0	1,8%	1,8%
6	4 901,0	4,7%	4,7%
7	4 575,0	-6,7%	6,7%
8	4 688,0	2,5%	2,5%
9	4 657,0	-0,7%	0,7%
10	4 696,0	0,8%	0,8%
11	4 582,0	-2,4%	2,4%
12	3 435,0	-25,0%	25,0%

in the text box I use:

=avg(fabs(aggr(SUM(Alpha)/(above(Sum (Alpha),1))-1,WEEK_NBR)))so if I use the same expression with aggr in text box

I get 0.1613.... which is clearly not the case

Re: Creating average from "above" created expression in a chart

sunny_talwar — Wed, 30 Mar 2016 17:53:11 GMT

I think you should pay attention to what Stefan mentioned. I forgot to address the sorting within the aggregation which has been explained in detail by Stefan. If you have QV12, the solution would be fairly quick and simple, anything old, you would have to fix the sorting in the script.

Re: Creating average from "above" created expression in a chart

Wed, 30 Mar 2016 18:12:50 GMT

thanks Stefan, I will do it tomorrow but if I am using cycling grouping by week/ month / quarter and year as dimension(i can replace week_nbr with the name of the group)..how should the load order look like

btw I am with QV 11

Re: Creating average from "above" created expression in a chart

sunny_talwar — Wed, 30 Mar 2016 18:18:39 GMT

I think if you can create a master calendar first thing in the script, you are set with all your date related field to be sorted in the correct manner.

Re: Creating average from "above" created expression in a chart

Thu, 31 Mar 2016 13:47:41 GMT

Ok, I created a master calendar before loading the data but now I hve another problem - it seems that both tables does not communicate

this is the script I used (no need to join in in the script as the data table as a DATE field as well - so in table relationships the join is present, however it simply does not work) - it works only on daily basis - if i put weeks or months as dimensions for example - it just shows the total

LET Start = floor(YearStart(AddMonths(today(), -12*3)));
LET End = floor(YearEnd(AddMonths(today(), 12)));
LET NumOfDays = End - Start + 1;

Date_src:
LOAD
$(Start) + Rowno() -1 as DateId
AUTOGENERATE $(NumOfDays);

Calendar:
LOAD
       date(DateId) AS DATE,
       Week(DateId) as Week,
       Month(DateId) as Month


RESIDENT Date_src;

Drop Table Date_src;

LET Start = null();
LET End = null();
LET NumOfDays = null();

Re: Creating average from "above" created expression in a chart

sunny_talwar — Thu, 31 Mar 2016 14:34:55 GMT

Change the DATE to the name you have in your Fact table:

Calendar:
LOAD
      date(DateId) AS DATE,
      Week(DateId) as Week,
      Month(DateId) as Month


RESIDENT Date_src;

Re: Creating average from "above" created expression in a chart

swuehl — Thu, 31 Mar 2016 14:40:51 GMT

You need to replace the Aggr() dimension with the chart dimension like

=avg(fabs(aggr(SUM(Alpha)/(above(Sum (Alpha),1))-1, [$(=GetCurrentField( GroupName ))] )))

Replace GroupName with your dimension group name.

Re: Creating average from "above" created expression in a chart

Thu, 31 Mar 2016 14:50:01 GMT

It is all solved now. The master calendar was the key indeed. BEFORE the loading of the SQL Server data.