Solved: Re: What if analysis. How to move values in time? - Qlik Community

andy · ‎2019-05-09

Hi folks,

I would like to have your input on a challenge I'm facing. Normally what if analysis as shown by Qlik is just about scaling a measure. This is about "moving" values in the data like using addmonths() in the script answering what if we produce this order 1 month earlier and another 2 months later?

The users uses Qlik Sense today to look at current production planning and material demand.

Now they want to be able to move the individual projects in time, that is left and right on the x-axis in the chart below. Like Addmonths(startdate,offset) in the script but they want to be able to do this without having to wait for a scheduled task to reload the application. Also each user should see the original data all the time in the application not being influenced by some other user moving a project 3 months in time.

Any user who added an offset to a project shall be able to store that offset and retrieve it next time.

I've been thinking about

- on demand app generation (ODAG)

- extensions which modifies the underlying data-set so all charts are updated in a session

- mashups with crossfilter and own charts.

I attached a simple qvf to show how it could look like.

Any ideas are highly appreciated!

brgds

Andy

sunny_talwar · ‎2019-05-09

For selection in YearMonth field... may be change it to a selection in a calculated filter dimension.

=Aggr(Only({1} AddMonths(YearMonth, Alt(Pick(Match(Project, 'Proj A', 'Proj B', 'Proj C', 'Proj D', 'Proj E', 'Proj F'), $(vProjA), $(vProjB), $(vProjC), $(vProjD), $(vProjE), $(vProjF)), 0))), Key)

Where Key is a field created by combining YearMonth and Project in the script

LOAD *,
	 YearMonth&Project as Key;

For the changing Projects, you can dynamically create the variables in the script like this

FOR i = 1 to FieldValueCount('Project');

LET vFieldValue = FieldValue('ProjectWithoutSpaces', $(i));
LET $(vFieldValue) = 0;

NEXT

Now your chart dimension can be this

=Aggr(AddMonths(YearMonth, Alt(Pick(Match(ProjectWithoutSpaces, $(=Concat(DISTINCT Chr(39) & ProjectWithoutSpaces & Chr(39), ', '))), $(=Replace(Concat(DISTINCT '#(' & ProjectWithoutSpaces & ')', ', '), '#', '$'))), 0)), Project, YearMonth)

and filter dimension

=Aggr(AddMonths(YearMonth, Alt(Pick(Match(ProjectWithoutSpaces, $(=Concat(DISTINCT Chr(39) & ProjectWithoutSpaces & Chr(39), ', '))), $(=Replace(Concat(DISTINCT '#(' & ProjectWithoutSpaces & ')', ', '), '#', '$'))), 0)), Key)

View solution in original post

sunny_talwar · ‎2019-05-09

Move all projects to a certain date or a select few? I am not sure I completely understand...

andy · ‎2019-05-09

Hi,

Individually the projects shall be possible to move back and forth in time.

Lets say we want to see how it looks like if Proj C is started 2 months earlier than current plan and Proj E is started 3 months later.

Regards

Andy

sunny_talwar · ‎2019-05-09

My knowledge of Qlik Sense is rather limited, but ODAG basically re-runs the dashboard, so I am not sure if that serves the purpose. But If you have limited number of projects, can you not use a variable offset for each of the project and adjust the timeline based on the user input. For instance they can enter -2 for Project C, if they want to adjust it to be starting 2 months earlier or 2 if they want to adjust it to be starting 2 months later

andy · ‎2019-05-09

Well, I guess you cannot do that without a reload of the script...

Changing the x-axis of the chart will move all the projects.

sunny_talwar · ‎2019-05-09

What can you not do without a reload? the variable option? I think variable can work for individual projects without a reload.

andy · ‎2019-05-09

Sorry, I don't see how that should work.

Would you mind to modify the app or post another simple app to show how you would do that?

sunny_talwar · ‎2019-05-09

Something like this

When vProjC is 0

When vProjC is -2

Does this make sense?

andy · ‎2019-05-09

Hi,

That is a helpful suggestion, thanks!

It gets kind of weird when doing a selection though

sunny_talwar · ‎2019-05-09

Right, what would you like to see when you make selection in YearMonth field?