What if analysis. How to move values in time?

[andy]

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

[andy]

I think the user would like to see the month selected also in the chart. Which seems impossible and might be a limitation they can live with.

How would you do when the number of projects change at every reload, need to dynamically assign the calculated dimension as well as the variables and the GUI-part...

I guess there will not be more than lets say 30 projects.

[sunny_talwar · Accepted solution]

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)

[andy]

Hi,

Thanks for your effort!

I think it works even better with your filtering-dimension used in the charts as well.

Any ideas how the automatically created variables shall be presented in the GUI to adapt for different number of variables in a nice manner? Create an extension perhaps...

brgds

Andy

[sunny_talwar]

This is the part I am not sure about. May be instead of assigning customized variable, may be we create generic variable say v1, v2, v3 so that we can always have them with the same value. Will need to play around with the idea to get it to a point where you understand it's limitations, but yet work to some degree of satisfaction.

[andy]

Hi Sunny,

I think  you answered the question I asked so I'll mark it as answered.

Nice done!

My real case is somewhat more complicated though so maybe I'll go for a session app that is generated from the standard app after the user has selected his offsets anyway.

Thanks for your effort.

Regards

Andy