Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
I suggest looking into The As-Of Table
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
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!
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
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?
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.
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)
I quickly made a schematic of how it looks now.
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;