Current vs Previous month Stock

[agnie_john]

Hi,

I have a Fact table which contains Year Month Wise Stock(Inventory) and Shipped Quantity details.

I would like to show a bar chart having Year Month as Dimension with two measures

   1. Inventory Quantity

   2. Shipped Quantity (3 months Before)

For example 

     Inventory of each month as bar chart. I need to show one more bar with shipped quantity of  YearMonth-3

Thanks,

John

[G3S]

try using addmonths function to calculate the value for 3months prior

[Aditya_Chitale]

you cannot use set analysis here, as set analysis calculates value only for 1 row and not all rows.

Try using above() function but keep in mind that your chart should have only 1 dimension (in your case Year Month) and arrange the Year Month dimension in ascending order in chart.

Expression:

=Above(Sum(ShippedQty),3)

Sample Data Used:

Output:

Regards,

Aditya

[John_Peter]

Hi @Aditya_Chitale

Thanks for your reply. 

 understand that i can use above function,my problem is instead of three i want to display  shipping qty of 11 months before.even though the chart show 12 months data i would like to show 11 month old data for each row.

I think it is not possible i will go with load script.

Please let me know if any other method to achieve this.

Regards,

John.

[Aditya_Chitale]

Hi @John_Peter ,

Sorry for the late reply. Hope you have solved the problem by now. If not,  please try below solution achieved using script load editor.

I have calculated previous 3 month Shipped Qty values for each Date in the script itself. You can replace the digit '3' with any number for showing previous  N month values according to your need. The months which don't have adequate previous months for calculating previous N month values will show the current  month values against them.

Fact:

load *,

Date(Date#(DateNew,'DD/MM/YYYY'),'YYYYMM') as MonthYear;

load *,

Date(Date#(Date,'DD/MM/YYYY'),'DD/MM/YYYY') as DateNew;

load * Inline

[

Date,InventoryQty,ShippedQty

16/01/2020,5,8

16/02/2020,6,9

16/03/2020,7,10

16/04/2020,8,11

16/05/2020,9,12

16/06/2020,10,13

16/07/2020,11,14

16/08/2020,12,15

16/09/2020,13,16

16/10/2020,14,17

];

NoConcatenate

ShippedQtyTable:

load *,

if(recno()>3 , peek(OldShippedQty,-3), OldShippedQty) as NewShippedQty,

    Date(Date#(DateNew,'DD/MM/YYYY'),'YYYYMM') as MonthYear;

load

DateNew,

    ShippedQty as OldShippedQty,

    InventoryQty as NewInventoryQty 

Resident Fact order by DateNew asc;

drop table Fact;

In chart expression, simple use the newly created fields for showing sum.

eg: sum(NewShippedQty)

Output:

Regards,

Aditya