topic Re: Re: Accumulated amounts Graph without Projection in QlikView

Accumulated amounts Graph without Projection

shyamcharan — Tue, 25 Nov 2014 01:37:04 GMT

Hi Experts,

I have a requirement where I show up the Actual Cost and the Forecast costs in one graph.

The graph should show the accumulated costs of Actual and the Forecast and the Forecast should start where the Actual ends.

But What i could not get is to remove the projection line for the actual cost.

When i use Accumulation option in the chart the actual line goes beyond its Actual Year as a standard projection.

But I do not want the graph to show up the line beyond its Year of Cost. If the Actual amounts are till 2002 the line should stop at 2002 with accumulation.

And the Forecast should start at where the accumulated Actual Cost stops.

I have attached the QVW what i could get. I appreciate any suggestion on this.

Thanks in advance.

Regards,

Shyam.

Re: Accumulated amounts Graph without Projection

Anonymous — Tue, 25 Nov 2014 02:50:28 GMT

Hey,

PFA.

Hope this is you are looking for.

Best Regards,

Chinna

Re: Re: Accumulated amounts Graph without Projection

shyamcharan — Tue, 25 Nov 2014 03:00:34 GMT

Hi Chinna,

Thanks for the quick response.

The solution seems to be a clever one and it would work if I have only 2 cost types.

But in my requirement I would have more than one forecast and I would also have Planned and other type of lines.

Please find the attached where I have added Planned details into the QVW.

Now I would want the Actual to be accumulated and only till its specific year values and then the Forecast and Planned to show up right from where the Actual stops.

Thanks heaps in advance.

Shyam.

Re: Re: Re: Accumulated amounts Graph without Projection

Anonymous — Tue, 25 Nov 2014 03:14:10 GMT

Hey,

PFA..

Make use of appropriate colors for text boxes to keep the illusion standby.

BR,

Chinna

Re: Re: Accumulated amounts Graph without Projection

shyamcharan — Tue, 25 Nov 2014 03:26:50 GMT

Thanks again Chinna.

May be its my mistake for not providing enough information. I apologise for that.

My graph would be so dynamic that the forecasts are created on the go and these new forecasts are pulled into the graph and reloaded.

Moreover, the user might create more than 10 or even 20 forecasts.

The forecasts or planned are not something we would know before. and In forecasts I would have Forecast 1 or Forecast 2 or ....Forecast 15.

Thanks for the quick turn around by my requirement quite more complex that we got so far.

If I could somehow get to know how i can get Aggregated amounts into the graph then I could add this amount to the Forecasts or other line amounts to start from where Acutal ends. This is just my assumption. There might better ways of doing it.

So I would have Actuals for a specific set of years and the users would make forecast for the next few years and this could different forecasts not just one forecast.

They are done on the go not predefined.

Please let me know if anything is not clear.

Regards,

Shyam

Re: Re: Accumulated amounts Graph without Projection

Anonymous — Tue, 25 Nov 2014 03:43:51 GMT

Hi ,

In Qlikview if you set the full accumulation and if you take actual , forecast and planned are a separate expressions, Actual graph will be continued to the forecast years.

that's the reason I'm just using the Cost type flag. and keeping the Actual while calculating both forecast and Planned.

And it will work even if you have forecast for multiple years.

if you mean that you have multiple types of forecast and multiple types of actuals also, you may have to create new expressions.

I'm littile bit confused with the above description. You may attach possibily of other forecast and planned, will try to sort.

BR,

Chinna

Re: Re: Accumulated amounts Graph without Projection

shyamcharan — Tue, 25 Nov 2014 04:22:09 GMT

Hi Chinna,

Yes I would get a copy of what I have and will post here.

Thanks,

shyam

Re: Re: Re: Accumulated amounts Graph without Projection

shyamcharan — Tue, 25 Nov 2014 10:33:54 GMT

Hi,

For now I could only get a screenshot of what my graph has so far.

It has multiple forecast lines.

They are not as required.

I would need to show up an accumulated Actual line and then the Accumulated Forecast lines which start where Actual ends.

Re: Re: Re: Re: Accumulated amounts Graph without Projection

Anonymous — Tue, 25 Nov 2014 11:04:29 GMT

Hey,

You can create the chart in the same way as I created for Cost type. You have to create for Forecast Type instead..It will work..

Best regards,

Chinna

Re: Re: Re: Re: Accumulated amounts Graph without Projection

shyamcharan — Tue, 25 Nov 2014 11:12:25 GMT

Hi Chinna,

Thanks. Before I could try that Can i get a formula that works to get an accumulated cost for my example.

Also, I would like to see the accumulated cost only to-date not for the entire date range.

Though the graph has years from 2000 to 2006 The accumulated has to show only until 2004 where the Acutal Cost ends.

This might help me a bit.

Regards,

Shyam