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: 
Not applicable

Creating average from "above" created expression in a chart

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

16 Replies
sunny_talwar

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.

Not applicable
Author

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

sunny_talwar

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.

Not applicable
Author

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

sunny_talwar

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;

swuehl
MVP
MVP

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.

Not applicable
Author

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