creating a line chart with different values and dimension

[MichaelFL]

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 ? 

[chrismarlow]

Hi,

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

Cheers,

Chris.

[MichaelFL]

[chrismarlow]

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
];

[MichaelFL]

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.

[chrismarlow]

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.

[Brett_Bleess]

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