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)

[Clever_Anjos]

Is it doubt or you want us to make the application to you?

[#3]

If you can make the app then that will be very helpful.
I am struggling to show the months in order in the chart. I get them in the reverse order for the default. With this user selection, I am getting no where close to the requirement.

[Clever_Anjos]

You should try your best, and then post where you stopped,

If you don´t try, will never learn

Best regards

[Clever_Anjos]

I´ve started it for you

[MayilVahanan]

Hi

For set analysis

Please refer this pdf.

I did somewhat yours requirement.  But learn set analysis.

As Clever said, Practice yourself.

[#7]

Hi Mayil,

Thank you very much. That was really helpful. I am not yet well versed with set analysis and trying to get a good grip on it.

Your solution is exactly what I was looking for. But there is still a small issue here.

I don't have access to the database. I have QVDs(10) only to work on. So I am using a dynamic loop to load the QVDs instead of individual load and I can't modify the load script. The data is huge and number of fields in each of the QVDs are very high.

In your qvw, you are getting the DateNum and Inventory, through the load script. I can't get those through the load script (as per the existing QVD loading logic).

I tried to manipulate the set analysis used in your line chart but I got null in the output. I don't have the DateNum and Inventory fields. So I am doing the conversion directly in the set analysis expression.

Can you please advise?

Thank you.

[#8]

Hi Clever,

Thank you very much. I was able to get to this point.

But the order of months is reversed. Also when you select a new month, then it shows for all the months. I need only for 6 months old data from that period.

[MayilVahanan]

Hi

Can you please explain why you can't able to create datenum field or inventory ?

What expression are you using now?

[#10]

I can't create the datenum and inventory because I am loading the QVDs dynamically through for loop.

I am trying to replace the expression in the line chart with this:

Your expression: Num(AddMonths(Max(DateNum),0))

Mine: Num(AddMonths(Max(date#(YearMonth,'YYYYMM')),0)))

But issues with the set analysis comparison