topic creating a line chart with different values and dimension in QlikView

creating a line chart with different values and dimension

MichaelFL — Wed, 11 Dec 2019 13:36:02 GMT

Hello, I would like to create a line chart with the target to make the future stock development visible.

Unfortunatly our data model is sometimes not usable.

As basis we have a table with current Stock value as monthly value - The time period is month/year

The 2nd value includes the COG - also as monthly value - The time period is year and month

The 3rd value includes the future goods receipts. The time period is on daily base .

The calculation should be as follow. Current - COG + future goods receipt.

The problem is because of different dimensions. In the data model there is no join between the tables.

Is it possible to make a calculation without any loading of data before ?

Re: creating a line chart with different values and dimension

chrismarlow — Wed, 11 Dec 2019 20:34:51 GMT

Hi,

Can you share some made up/sample data with a picture of what the output would look like?

Cheers,

Chris.

Re: creating a line chart with different values and dimension

MichaelFL — Thu, 12 Dec 2019 13:53:19 GMT

Re: creating a line chart with different values and dimension

chrismarlow — Thu, 12 Dec 2019 21:03:42 GMT

Hi,

There will be a more elegant way of setting up the ValueList (or using ValueLoop), but the following may help (based off the toy script at the bottom);

Dimension holds ValueList;

Expression uses the dates in ValueList;

Hope this gets you started, sure it can be improved.

Cheers,

Chris.

PS my dates are in UK format

future_goods: LOAD * INLINE [ FG Date, FG Value 11/12/2019, 1000 12/12/2019, 3000 06/01/2020, 5000 05/02/2020, 1000 ]; current_stock: LOAD * INLINE [ CS Date, CS Value 01/12/2019, 5000 ]; cog: LOAD * INLINE [ COG Date, COG Value 01/12/2019, 2000 01/01/2020, 1000 01/02/2020, 2000 ];

Re: creating a line chart with different values and dimension

MichaelFL — Fri, 13 Dec 2019 06:45:51 GMT

First of all thank you for providing a solution. It is understandable.

My question in this regard is how it works with the different formats of dimension.

For example, one loadscipt is with 12.2019 the other with Dec.2019.

Unfortunately, I have no access to change the scripts in the load editor. I have to find a solution with the function editor only.

Re: creating a line chart with different values and dimension

chrismarlow — Fri, 13 Dec 2019 08:53:02 GMT

Hi,

You are going to have to get into the (in my opinion) thorny world of manipulating dates (is not my favourite bit of QlikView). So something like the below change to the expression referencing the month only may work;

sum(If(Date#('01-'&[CS Date],'DD-MMM-YY')<=MonthEnd(Date(ValueList('01/12/2019','01/01/2020','01/02/2020'))),[CS Value],0))

Let me know if that helps.

Cheers, Chris.

Re: creating a line chart with different values and dimension

Brett_Bleess — Wed, 18 Dec 2019 16:35:11 GMT

Michael, I think Chris got you on the right track, here are a couple of Design Blog links that may prove useful:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Date-Function/ba-p/1463157

https://community.qlik.com/t5/Qlik-Design-Blog/Why-don-t-my-dates-work/ba-p/1465849

Here is the base Design Blog URL in case you want to do some searching yourself too, there are hundreds of posts out here, all from our internal experts and vast majority are how-to related.

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Do not forget to use the Accept as Solution button on any posts you get that help you with a solution too, this gives the poster credit for the help and lets the other Community Members know what worked...

Regards,
Brett