topic Re: Trendline Week in QlikView

Trendline Week

kris_vliegen — Thu, 13 Oct 2016 08:53:13 GMT

Hi All,

I would like to make charge with a trendLine.

We have a dashboard with volumes petrol from fuelstations by day.

Because the volume in a weekend is less then a weekdayvolume. We would like to see a volume by day. But the volume in the graph would be the volume + the volume of the previous 6 days (so we have one week). Can I do this in the chart?

Or do I have to do this in the script?

The volume for the 10th of setptember in the graph should be the volume from 4 + 5 + 6 + 7+ 8 + 9 +10th of september.

Regards,

Kris

Re: Trendline Week

sunny_talwar — Thu, 13 Oct 2016 09:35:54 GMT

May be something like this

RangeSum(Above(Sum(Volume), 0, 6))

Re: Trendline Week

johanlindell — Thu, 13 Oct 2016 10:03:24 GMT

One solution is to make a special "rolling" 7 days dimension pointing to you calendar. In this "rolling 7 days"-table you will for each "rolling 7 date" have 7 rows of each "rolling 7 date" pointing to this date, the day before etc until 6 days before. Then you just switch your chart to having this new field as dimension:

Date, Rolling 7 date

2016-10-13, 2016-10-13

2016-10-12, 2016-10-13

2016-10-11, 2016-10-13

2016-10-10, 2016-10-13

2016-10-09, 2016-10-13

2016-10-08, 2016-10-13

2016-10-07, 2016-10-13

2016-10-12, 2016-10-12

2016-10-11, 2016-10-12

2016-10-10, 2016-10-12

2016-10-09, 2016-10-12

2016-10-08, 2016-10-12

2016-10-07, 2016-10-12

2016-10-06, 2016-10-12

//Johan

Re: Trendline Week

kris_vliegen — Thu, 13 Oct 2016 11:27:05 GMT

Hi All,

thaks for both the responses.

I've created the trendvolume in the script.

Like this

Load KeyTrend, // where KeyTrend = Site_Id & Article

DatumNum,

Volume ,

If(KeyTrend = Peek(KeyTrend), Peek(Volume) + Volume, Volume) as Volume2,

If(KeyTrend = Peek(KeyTrend), Peek(Volume2) + Volume, Volume) as Volume3,

If(KeyTrend = Peek(KeyTrend), Peek(Volume3) + Volume, Volume) as Volume4,

If(KeyTrend = Peek(KeyTrend), Peek(Volume4) + Volume, Volume) as Volume5,

If(KeyTrend = Peek(KeyTrend), Peek(Volume5) + Volume, Volume) as Volume6,

If(KeyTrend = Peek(KeyTrend), Peek(Volume6) + Volume, Volume) as TrendVolume

resident TrendTemp1 order by KeyTrend, DatumNum;

Regards,

Kris

Re: Trendline Week

kris_vliegen — Thu, 13 Oct 2016 11:33:21 GMT

Hi Sunny,

this works but the first 6 days of a month it doesn't show the correct values.

Re: Trendline Week

sunny_talwar — Thu, 13 Oct 2016 11:35:09 GMT

Try this:

RangeSum(Above(Sum({1}Volume), 0, 6)) * Avg(1)

Re: Trendline Week

sunny_talwar — Thu, 13 Oct 2016 11:37:12 GMT

Actually, instead of using 1, you would need to ignore date related field because one will ignore all selections, but I am not sure what all date fields you have where you plan to make selections, but those are the ones I would ignore. You will still see the selected area in your chart because of the use of Avg(1) which will be 0 outside of the selection. RangeSum() will take place over all the data included the unselected dates.

Re: Trendline Week

kris_vliegen — Thu, 13 Oct 2016 13:20:45 GMT

If I ignore the date related field. QV count all the first days of each month, all the second days, ...

So the result is not what I expected.

I have now calculated the trend in the script and this is working fine for me.

Regards,

Kris

Re: Trendline Week

sunny_talwar — Thu, 13 Oct 2016 13:24:43 GMT

I don't really know your setup, but if you want to take this route you can provide a sample to work with and I can look into it

Re: Trendline Week

MarcoWedel — Thu, 13 Oct 2016 20:27:41 GMT

Hi,

there might as well be an easier solution to your question using the chart integrated accumulation function:

hope this helps

regards

Marco