Solved: Re: "Trendline" in Pivot - Qlik Community

Report Inappropriate Content · ‎2014-10-22

Hello,

I have Pivot table as shown in attachment. It has two expressions =sum(TOT) (total sales so far) and =sum(BUD) (sales budget). Dimension on the top is months.

1) Now I would like to have TOT sums for the rest of the months which are not ready on the basis of previous months. In the expression what I'm looking after is that I would count Full months sales so far divide by the amount of monts and the I would have sales per month which I could use in the total sales. This is accurate enough. In the chart I would also like to these numbers which are counted based on estimation to have another color so they would be noticed. One illustration is shown below.

1	2	3	4	5	6	7	8	9	10	11	12	Tot
2	3	2	3	2	3	2	3	2	2,44	2,44	2,44	29,33

2) And another newbie question is that I have total sums now on the right side Total TOT and BUD, how can I have another column on the end to have calculation TOT minus BUD?

JonnyPoole · ‎2014-10-30

Just rereading this thread. I didn't upload the last sample so apologies for that.

Here i added multiyear support.

I made an assumption that a user would only look at 1 year at a time when viewing this chart so i set it up that way.

take a look and test it out.

View solution in original post

JonnyPoole · ‎2014-10-22

You can use an IF() statement in your expression formula to conditionally calculate the sum(TOT) or the average of preceding months. It can be completely dynamic with the number of months.

It would be best if you can post some sample data (CSV , XLS etc...) that we can use to show possible techniques.

Report Inappropriate Content · ‎2014-10-23

Okay, here is one very simple example data.

JonnyPoole · ‎2014-10-23

Great.

Here is a sample. It will work for this data set.

i calculated a month field in the load (you see it below in the column headers) and then used an IF() statement to figure out if the month is >= to the month of Today. The month of today I highlighted in red. In green are the forecast Totals based on the average of the preceding months.

Couple of things ... will your data have multiple years ? will the current month always be today's month ? Last month ? or does it change ?

Report Inappropriate Content · ‎2014-10-24

Thanks for this so far. Almost there.

But Yes we have multiple years. So if 2013 is chosen then we don't have any forecast on rest of the year. Forecast is always based on today's month.

And forecast is calculated in this example when we are on October based on the full months Jan-Sep and not counting Oct figures so far. So for item 123 we have sales of 35 and forecast would be then 35/9=3,9 for Oct-Dec of 2014.

And idea for the Diff would be that I would have that Diff amount only at the end. Not monthly.

JonnyPoole · ‎2014-10-24

Leaving the multiyear aside for a second...

I added a 'TOT Partial' field in the data model to take into account the current months values. I use that to subtract current months total so as not to interfere with the average.

What if its January, how do you forecast February -> December as there is no average to go off ?

Report Inappropriate Content · ‎2014-10-27

Forecast is not done in January. When we are living in January there is no point to make forecasts for the rest of the year when we have so short time period behind. So I assume that if we would look this chart at January it would show 0 for the rest of the year and that's just fine.

JonnyPoole · ‎2014-10-27

Ok. I think the solution should give the right monthly numbers but ... for the Totals, would you include the forecasted numbers or just the YTD totals ?

Also the question about the DIFF showing only on the Totals ... it is possible but not straightforward. There is a klugy relatively simple method and a more involved data model based solution. But lets go through the question above first..

Report Inappropriate Content · ‎2014-10-28

First of all, thanks for your input on this issue.

Totals in the end are fine as it is now, meaning that showing estimates in the sum total is okay.

And just for clarification that the DIFF in the end is okay now. Only want to get rid of the monthly DIFF column.

JonnyPoole · ‎2014-10-28

I fixed up the TOTALS to include the forecasted numbers.

I also went with the klugy yet relatively easy solution of adding the DIFF in the BUD cell. This eliminates the problem of not being able to suppress monthly columns while showing an aggregate for that same column.

See what you think. As i alluded too before , you can get DIFF to appear in the aggregate columns as a unique column but its more technical (out of box solution) that involves changing up the data model to introduce a new table that will include a new month field that includes 'TOTAL' as a month (no joke) and associate TOTAL to all months in the year.