topic Re: Re: 6 months Line Chart in QlikView

6 months Line Chart

Mon, 18 Nov 2013 19:41:49 GMT

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.

We need to plot the month wise inventory for each product per store, Inventory = (Qty in stock - Qty sold)
A multi box for selection criteria. The multi box should have, store, Product, yearmonth to choose values from.
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)

Re: 6 months Line Chart

Clever_Anjos — Mon, 18 Nov 2013 19:43:00 GMT

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

Re: 6 months Line Chart

Mon, 18 Nov 2013 19:47:58 GMT

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.

Re: 6 months Line Chart

Clever_Anjos — Mon, 18 Nov 2013 19:48:56 GMT

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

If you don´t try, will never learn

Best regards

Re: Re: 6 months Line Chart

Clever_Anjos — Mon, 18 Nov 2013 19:58:24 GMT

I´ve started it for you

Re: 6 months Line Chart

MayilVahanan — Mon, 18 Nov 2013 20:07:33 GMT

For set analysis

Please refer this pdf.

I did somewhat yours requirement. But learn set analysis.

As Clever said, Practice yourself.

Re: 6 months Line Chart

Tue, 19 Nov 2013 15:21:26 GMT

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.

Re: 6 months Line Chart

Tue, 19 Nov 2013 15:27:07 GMT

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.

Re: 6 months Line Chart

MayilVahanan — Tue, 19 Nov 2013 18:59:15 GMT

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

What expression are you using now?

Re: 6 months Line Chart

Tue, 19 Nov 2013 19:21:33 GMT

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

Re: 6 months Line Chart

MayilVahanan — Tue, 19 Nov 2013 19:46:00 GMT

can you provide a sample data?

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

Re: Re: 6 months Line Chart

Wed, 20 Nov 2013 19:06:00 GMT

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?

Re: Re: Re: 6 months Line Chart

MayilVahanan — Wed, 20 Nov 2013 19:26:47 GMT

PFA