10 Replies Latest reply: Aug 3, 2018 10:55 AM by Kuba M RSS

    Warehouse analysis - period to period

    Kuba M

      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.

        • Re: Warehouse analysis - period to period
          Sunny Talwar

          I suggest looking into The As-Of Table

          • Re: Warehouse analysis - period to period
            Sibin Jacob.C C

            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

              • Re: Warehouse analysis - period to period
                Kuba M

                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!

                  • Re: Warehouse analysis - period to period
                    Sibin Jacob.C C


                    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



                      • Re: Warehouse analysis - period to period
                        Kuba M

                        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?

                  • Re: Warehouse analysis - period to period
                    Kuba M

                    So in light of the above, all advices remains in effect?