Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

qlikeers
New Contributor

Warehouse analysis - period to period

Hi!

I have a problem with showing warehouse data.

Let's assume that I have a fact table (warehouse data of the end of each day - i.a.: data, product, stockqty, stockval, etc.), and several tables with dimmensions.

I would like to show data at the end of the period (eg month) and for the same period in the previous year. (assuming that the data for June 2018 is the data as of the last day of June 2018).

I attach an example of what I would like to achieve.

1 Solution

Accepted Solutions
sibin_jacob
Contributor III

Re: Warehouse analysis - period to period

It is looking good,

Keep Last_Date_Month_flag column also in the Dim-MonthEOP table.

It will helpful for writing the chart expression.

Load Date,Month(EOP), 1 as Last_Date_Month_flag

resident Dim_MonthEOP;

10 Replies

Re: Warehouse analysis - period to period

I suggest looking into The As-Of Table

sibin_jacob
Contributor III

Re: Warehouse analysis - period to period

Please create flag for last date in a Month using the below script.

//Fact_table is the existing table

Table2:

Load Date, product, stockqty, stockval,

if(Month(Date)=previous(Month(Date),0,1) as Last_Date_Month_flag

resident Fact_table order by Date desc;

Use Last_Date_Month_flag column for filtering your data,


only last date in each Month will get value as 1

Dimension

Month column

First Expression

Sum({<Last_Date_Month_flag={1},Year={2017}>}stockqty)


Second Expression

Sum({<Last_Date_Month_flag={1},Year={2018}>}stockqty)



Thanks,

Sibin

qlikeers
New Contributor

Re: Warehouse analysis - period to period

Thanks Sibin!

It seems to me that it works

But I have 3 more questions:

1) How to limit the chart to display only the months from January to the current one?

2) If I have a table (related to the fact table), as in the attachment, can I use it instead of your "Table2"?

3) Is it possible to do that if I choose a month on the filter (eg 06/2017) then I will see the current month and the same month, but a year earlier?

Many thanks!

sibin_jacob
Contributor III

Re: Warehouse analysis - period to period


1) How to limit the chart to display only the months from January to the current one?


It is better to use MasterCalender script to do Date, Month, Year calculations.

Here you can create a Month column using the Date column.

Date(Date,'MM') as Month_Value

This new column will have values like 1,2,3,4 etc for each month.

Month(Date) as Month_Name


use Month_Name in Dimension

like this:

if(Month_Value<=vMaxMonth,Month_Name) 


create a variable vMaxMonth


Definition for the variable:

=Date(Max(Date),'MM')


2) If I have a table (related to the fact table), as in the attachment, can I use it instead of your "Table2"?

Yes, Then you can use that table directly.


3) Is it possible to do that if I choose a month on the filter (eg 06/2017) then I will see the current month and the same month, but a year earlier?

Yes, Please use the MasterCalender.

You can achieve this using set analysis also.


Thanks,

Sibin



qlikeers
New Contributor

Re: Warehouse analysis - period to period

Sibin Jacob.C C napisał(-a):

...


2) If I have a table (related to the fact table), as in the attachment, can I use it instead of your "Table2"?

Yes, Then you can use that table directly.

...

As far as I understood your way and it worked - I do not know how to use the table from the attachment (Time_PoP) to get the right effect.

3) Is it possible to do that if I choose a month on the filter (eg 06/2017) then I will see the current month and the same month, but a year earlier?

Yes, Please use the MasterCalender.

You can achieve this using set analysis also.

Choosing a specific month (eg 06/207) I do not restrict ("cut") data for the previous year?

sibin_jacob
Contributor III

Re: Warehouse analysis - period to period

2 ) I am assuming Time_POP table having only  last date for each month.

You can left join the Time_POP table with your existing table using the below script.

Table2:

Load Date, product, stockqty, stockval,

resident Fact_table ;

Left Join

Load Date, 1 as Last_Date_Month_flag

Resident Time_POP;

Now we have create the same Last_Date_Month_flag column.


You can use the same expression to get the result.


sibin_jacob
Contributor III

Re: Warehouse analysis - period to period

3) I am assuming you have column Month_Year and the value is 06/2017

User selected Month_Year from a list box

Current Year Expression:

Sum({<Last_Date_Month_flag={1},Year={2017},Month_Year=,Month_Value={06}>}stockqty)


Previous Year Expression:

Sum({<Last_Date_Month_flag={1},Year={2016},Month_Year=,Month_Value={06}>}stockqty)

qlikeers
New Contributor

Re: Warehouse analysis - period to period

I quickly made a schematic of how it looks now.

sibin_jacob
Contributor III

Re: Warehouse analysis - period to period

It is looking good,

Keep Last_Date_Month_flag column also in the Dim-MonthEOP table.

It will helpful for writing the chart expression.

Load Date,Month(EOP), 1 as Last_Date_Month_flag

resident Dim_MonthEOP;

Community Browser