Warehouse analysis - period to period

[qlikeers]

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.

[sunny_talwar]

I suggest looking into The As-Of Table

[sibin_jacob]

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]

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]

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]

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]

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]

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]

I quickly made a schematic of how it looks now.

[sibin_jacob · Accepted solution]

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;