6 months Line Chart

[#1]

There are 4 stores(1,2,3,4) and 3 products(P1, P2, P3). The data captured, Qty in stock and Qty sold, for these stores from Nov-2012 to Oct-2013.

Need to plot a line chart for 6 months as per the below requirement.

1. We need to plot the month wise inventory for each product per store, Inventory = (Qty in stock - Qty sold)
2. A multi box for selection criteria. The multi box should have, store, Product, yearmonth to choose values from.
3. By default, when the sheet is opened for the first time, the line chart should show the product P1 and its inventory in the last 6 months in all the stores.

e.g. In Nov-2013 (current month) the default line trend should be the inventory of product P1 from May-2013 to Oct-2013 in all the stores.

  5.  When a particular store/product is selected, then the trend should change accordingly as per the user selection

  6.  When a particular month is selected from the list (the selected month should be older than current month), then the line chart should        should show the inventory of 6 months older than that the selected month.

e.g. when the user selects, Aug-2013, then the trend should be from March-2013 to August-2013

when the user selects Feb-2013, then the trend should be from Oct-2012 to Feb-2013 (because no data available beyond Oct-2012)

[MayilVahanan]

Hi

can you provide a sample data?

Did you check -> Max(date#(YearMonth,'YYYYMM')) gives value?

[#3]

Hi Mayil,

Please check the attached. since I can not create the fields Inventory and DateNum in the load script, I am trying to do this in expression tab in the line chart. (see the line chart inventory 2). I know there is issue with the way I am using the expression to compare between two YearMonth values.

Here is the expression I am using:

=Sum({<YearMonth=, Num(Date#(YearMonth,'YYYYMM')) = {">=$(=Num(AddMonths(Max(Date#(YearMonth,'YYYYMM')),-5)))<=$(=Num(AddMonths(Max(Date#(YearMonth,'YYYYMM')),0)))"}>}([Qty in stock]-[Qty Sold]))

Can you please correct this?

[MayilVahanan · Accepted solution]

Hi

PFA