Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
Hi,
Can you share some made up/sample data with a picture of what the output would look like?
Cheers,
Chris.
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
];
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.
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.
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