16 Replies Latest reply: Jan 4, 2012 7:39 AM by Vijay Kumar

# Future Simulation without existing data, based on past data?

Hi,

I have e.g. turnover data for the last years. The calendar information is hold in a calendar table, going from e.g. 2000 to 2020. I would like to show in a diagram the turnover for the last years by period, AND how the turnover will develop in the next year based on the development of the current year. For the next year I do not have data.

I thought about using the calendar table for the dimension, and splitting the formula at today. For all dates below today I use the information I have, for all future dates, I calculate based on the values of this year. For example turnover growed by 2% from January 2011 to February 2011, by 3% the next month, and so on. So how does it develop for January 2012, based on the turnover in December 2011, growing by the calculated 2%.

When I try that, I am told that there are no information available... Do you have an idea or example for that?

Thanks a lot,

Martin

• ###### Future Simulation without existing data, based on past data?

Hi,

did indeed no one ever try this?

Martin

• ###### Future Simulation without existing data, based on past data?

hi

if i understood you properly

then try this

use an in put box add new variable (forecast%)

abd in your chart expresion use

Sum ([turnover.])*(1+forecast%/100)

thanks

rohit

• ###### Re: Future Simulation without existing data, based on past data?

Hi,

I attached an example.

For example I have sales-values for 2011 (and before). I do NOT have any values for 2012. I just generated the calendar-table including 2012.

In the example the upper diagram shows sales for 2011. Based on these values I want something like the lower diagram (created in Excel).

This one contains simulated values for 2012, based on the experience for 2011. e.g. since I know that sales increased by 154,- from dec.2010 to jan.2011, i want to estimate that the value for jan.2012 will be  dec.2011 + ( (jan.2011-dec.2010) * (1 + est.plus) ). And so on.

Was that understandable?

Martin

• ###### Re: Future Simulation without existing data, based on past data?

hi i undersyood ur point

try this

sum({<[Calendar Month Year]={\$(=max([Calendar Month Year])}>}Sales)

+

((sum((<[Calendar Month Year]={\$(=min([Calendar Month Year])}>}Sales)

-

sum((<[Calendar Month Year]={\$(=min([Calendar Month Year])-1}>}Sales)

*

(1+forecast/100))

thanks

rohit

• ###### Re: Future Simulation without existing data, based on past data?

Hi,

thanks for your response. I tried

=sum({<[Calendar Month Year]={\$(=max([Calendar Month Year]))}>}Sales)

+

((sum({<[Calendar Month Year]={\$(=min([Calendar Month Year]))}>}Sales)

-

sum({<[Calendar Month Year]={\$(=min([Calendar Month Year])-1)}>}Sales))

*

(1+forecast/100))

but the values are not only empty, I stil don't get dimension values for the 2012 periods.

Thanks,

Martin

• ###### Re: Future Simulation without existing data, based on past data?

hi

i think for getting value of you have to change your system date

may be i am wrong

thanks

rohit

• ###### Re: Future Simulation without existing data, based on past data?

Hi rohit,

Cheers,

Martin

• ###### Future Simulation without existing data, based on past data?

Hi rohit,

thanks for your last response (which disappeared somehow). If I got it right, it is actually not possible to create a simulation of data based on not-yet-existing date values, right?

Regards,

Martin

• ###### Future Simulation without existing data, based on past data?

hi Martin

wish you a very very happy new year,

as i discussed about your problem with other then i came to know

Forecasting is the toughest part in qlik view now  as per my knowledge.

that forecasting date should be in your database otherwise you can not able to create chart or whatever you want

regards

rohit

• ###### Future Simulation without existing data, based on past data?

Hi rohit,

thank you, the same for you! :-)

Hm, sounds bad. Sorry for having to ask again, I just want to exclude a misunderstanding on my side due to the language: So even if I create my calendar-table-records up to the end of e.g. 2012, I won't be able to create a simulation for that timeframe without existing data (turnover or whatever) in the same timeframe?

Thanks again so much,

Martin

• ###### Future Simulation without existing data, based on past data?

Hi Martin

I am not  able to get you

But let it elaborate more

now suppose you made calender from 1-1-2012 to 31-12-2012 and calculated the forecasting value by using your forecasting formula or on the bases on previous year data or whatever method you are using .

But these forecasting or target  date and data should be in your data base.so that qlikview can retrive that data from your datbase.otherwise how can you show these in  in front end.

regards

rohit

• ###### Future Simulation without existing data, based on past data?

Hi Rohit,

I think we are getting closer. :-)

Let me try to explain again:

- Assume that I created a calendar table with all days from beginning of 2010 until end of 2012

- For all first days in month (1st Jan, 1st Feb, 1st Mar, ..., 1st Dec) in 2010 and 2011 I have values, e.g. turnover (cumulated on month, so in the graph there is one value for Jan, Feb, Mar, and so on).

- But of course there are no turnover values for 2012 yet.

- This is why I want to simulate in a graph how business will look like in 2012, based on the values which I have for 2010 and 2011.

- So in order to create a value for Januar 2012, I want to add the difference between December 2010 and January 2011 to the value for December 2011.

- Let's say, in December 2010 there was a turnaround of \$5. In January 2011 the turnaround was \$7. So the difference is +2. Now I take the turnaround in December 2011 (which is e.g. \$12) and add the difference from the last year, so \$12 + \$2 = \$14.

- The same way I do it for all months in 2012, according to a formula like

"Turnover 1 month ago" + ("Turnover 12 months ago" - "Turnover 13 months ago")

- In the finished version I also want to include a forecast-variable but let's keep it simple for the moment.

Is it getting clearer?

regards,

Martin

• ###### Future Simulation without existing data, based on past data?

hi Martin

i understood your point very clearly,

but what value you calculate by using formula that value and date should be in your database before

Do you understhand what i want to say,???

otherwise i'll send you one demo application tommarow

regards

rohit

• ###### Future Simulation without existing data, based on past data?

Hi rohit,

I am afraid that I understood you. ;-) I was just still hoping that we misunderstood. So I do not have any other chance than generating the desired values in the script.

Regards,

Martin

• ###### Future Simulation without existing data, based on past data?

Hi Martin,

It would be better if you create the forecast values in script with help  of existing data and append it to the fact table do that you can be able to create charts as required.

Regards

VIjay

• ###### Future Simulation without existing data, based on past data?

Hey martin

First I want to be very thankful to you because of you i got some very important ideas.

from early morming i was mess up with your problem

1)For getting forecasting value all the calculation should be in beckend.

so you can generate new field

so what will heppen you can create a database in your backend from where you can retrive data in front end.

2)For maching the data you have to create new calender (e.g.for year =year+1,same date=month+12 etc.)

but matching of each date to previous date and map with previous data.

what i think you misunderstood  database word..i think you thought about your databse but i was talking about

beckend database...so sorry for that

Is it gettin clear

regards

rohit