topic Re: Help with Calculated Columns and Pivot Table - Urgent in QlikView

Help with Calculated Columns and Pivot Table - Urgent

Tue, 24 Sep 2013 17:40:06 GMT

Hello,

I am attempting to create calculated columns to calculate a 3/6/9 month running average in my data. It would be ideal to add these columns at the script level rather than calculate the values in expressions within visualizations; but I suppose the latter would suffice if it is easier to do and not too impactful on performance.

I need the column to take the sales for the month of a given row, then add the sales for the two previous months, and divide by 3 to get that months running 3 Month Average. Then do the same in two other columns except for 6 and 9 months. Obviously the values would not be accurate until the 9th month of data.

From there I will need to pivot the data into a pivot table to show the % change in the current months averages (3/6/9 month) compared to the previous months averages.

Attached is an excel doc for clarity. Its all sample data but should get the point accross. It shows existing columns, desired columns, and the desired pivot table.

This is rather urgent and ideally I need to figure this out today.

Thanks in advance for any help

Re: Help with Calculated Columns and Pivot Table - Urgent

pgrenier — Tue, 24 Sep 2013 18:44:10 GMT

Bonjour Stephan,

Here could be a way to load a three month running average using the data set provided:

LOAD Year

, Month

, Sales

, If(IsNull(Previous(Sales)) or IsNull(Previous(Previous(Sales))), Null(), RangeAvg(Sales, Previous(Sales), Previous(Previous(Sales)))) as [3 Month Average]

From [.\example1.xlsx] (ooxml, embedded labels, header is 2 lines, table is Sheet1);

Regards,

Philippe

Re: Help with Calculated Columns and Pivot Table - Urgent

Tue, 24 Sep 2013 18:59:21 GMT

Thank you fellow Frenchman!!

This is exactly what I needed for the first part.

One question however; will this only be accurate if I do an ORDER BY on my date fields? Basically, does the "Previous" logic simply look at the row above, or would it somehow know to look for previous by date fields? I assume it needs to be ordered by date which is perfectly fine.

Also, any ideas for the second portion??

Thanks!

Re: Help with Calculated Columns and Pivot Table - Urgent

whiteline — Tue, 24 Sep 2013 19:15:06 GMT

Hi.

The particular expressions depends on your real data model.

If you have calendar with YearMonth field it's easy, for example for 3MA:

=1-Avg({<YearMonth={'>$(=AddMonths(Max(YearMonth),-4))<=$(=AddMonths(Max(YearMonth),-1))'}>} Sales)/Avg({<YearMonth={'>$(=AddMonths(Max(YearMonth),-3))'}>} Sales)

There are constant value '22' in cell G29.

Re: Re: Help with Calculated Columns and Pivot Table - Urgent

pgrenier — Tue, 24 Sep 2013 19:20:29 GMT

Hello Stephan,

Yes, you are quite right, the ordering is important for this to work.

As for the second part, you could get the running values as such:

LOAD *

, If(IsNull([3 Month Average]) or IsNull(Previous([3 Month Average])), Null(), ([3 Month Average]-Previous([3 Month Average]))/[3 Month Average]) as [% Change];

LOAD Year

, Month

, Sales

, If(IsNull(Previous(Sales)) or IsNull(Previous(Previous(Sales))), Null(), RangeAvg(Sales, Previous(Sales), Previous(Previous(Sales)))) as [3 Month Average]

From [.\example1.xlsx] (ooxml, embedded labels, header is 2 lines, table is Sheet1);

Regards,

Philippe

Re: Help with Calculated Columns and Pivot Table - Urgent

jolivares — Tue, 24 Sep 2013 19:22:49 GMT

Check this... good lock

Re: Help with Calculated Columns and Pivot Table - Urgent

Tue, 24 Sep 2013 19:42:06 GMT

This is extremely helpful, thank you.

For the growth % however I don't think I need to create columns for it, I would rather do that in an expression as I will only be using it in one specific spot (in a pivot table that should look like the small 3x2 table in my original attachment).

One other concern though is that the sales data will be a mix of a whole bunch of different accounts. I assume I will need to order by the ACCOUNT and the DATE to get accurate numbers, but what happens when the records for one account ends and the next account begins? the first 3-9 rows for the new account will not be accurate because it will be including sales from the previous account in the average right?

Any solution to that?

Re: Re: Help with Calculated Columns and Pivot Table - Urgent

pgrenier — Tue, 24 Sep 2013 21:56:18 GMT

Hello again,

You shall find attached a new version of the QlikView document in which both types of calculations are available. By both types I mean to say "at load" and "in graph". Please note that I have added a new field during the load which represent each month's start date.

There is also a textbox in which you can see how to evaluate the most current date, the average for the last 3 months, as well as the previous month's rolling average. Finally, the evaluation method for obtaining the difference between the two.

Max Date = Max(MonthStart)

Cur Month Rolling Avg = Num(Avg({<MonthStart={">$(=AddMonths(Max(MonthStart),-3))"}>} Sales), '#.00')

Previous Month Rolling Avg = Num(Avg({<MonthStart={">$(=AddMonths(Max(MonthStart),-4))"}-{">$(=AddMonths(Max(MonthStart),-1))"}>} Sales), '#.00')

Difference = Num((Avg({<MonthStart={">$(=AddMonths(Max(MonthStart),-3))"}>} Sales) - Avg({<MonthStart={">$(=AddMonths(Max(MonthStart),-4))"}-{">$(=AddMonths(Max(MonthStart),-1))"}>} Sales))/Avg({<MonthStart={">$(=AddMonths(Max(MonthStart),-3))"}>} Sales), '+#0.0%;-#0.0%')

Regards,

Philippe

Re: Help with Calculated Columns and Pivot Table - Urgent

Fri, 27 Sep 2013 22:58:59 GMT

Hey Philippe,

Thank you, this was extremely helpful. If you don't mind I have a few questions regarding another visualization that I would need quick help with.

Basically I need to make a bar chart to show sales per quarter (one bar for each quarter) but also be able to have a reference line for each quarter which has a unique value (the sales GOALS for that quarter). We want to be able to quickly compare quarterly sales and how close they were to meeting or exceeding their goals. Should we use a different visualization?

We have tried Trellis but the auto-sizing of it does not work and we cannot use fixed columns/rows because the user may want to look at just one quarter, 4 quarters, or even 15 quarters (no limit really, we will use a horizontal scroll bar when there are over 4 bars, but we want a user to be able to scroll into historical quarters with ease)

We also tried using a container with 4 separate charts however this then limits the user to viewing 4 quarters, and also we cannot figure out how to make it so that a user can select Q4 of one year and compare it to Q1 of the next.

We tried using a combination chart and using a bar for the sales and then lines or symbols for the goals reference lines, but even with plataeu lines it looks really bad on the first and last bar where the line starts/ends in the middle of the bar rather than continuing to the end.

Please let me know if you have any suggestions! If you are willing to help in real time that would be amazing, I can PM you my direct email address if you want.

Thank you,

Stephan Cadieux

Re: Re: Help with Calculated Columns and Pivot Table - Urgent

pgrenier — Mon, 30 Sep 2013 13:43:49 GMT

Hello Stephan,

I sense you have two distinct needs in what you expressed above: 1) being able to look at n Quarters with an overall view, and 2) having the ability to compare a selected quarter with the previous quarter.

I you wish, maybe the attached new example file might be of some help for a trellis approach of visualizing 4 quarters at a time, with a scrollbar to see the data for the previous quarters.

Regards,

Philippe

P.S. If this isn't quite what you are looking for, maybe you could open a new issue so that the whole QlikCommunity may participate in helping you out?