topic Bar chart display based on previous day comparison in QlikView

Bar chart display based on previous day comparison

Tue, 12 May 2015 10:02:49 GMT

Hello,

I have a table with 3 columns:

Date Stock Coverage

01/01/2015 44 10

02/01/2015 0 30

03/01/2015 18 45

04/01/2015 0 12

05/01/2015 23 33

I have to create a bar chart with x-axis having date values.

On y-axis, i need to display the Coverage as follows:

ON x-axis, if stock of previous day is 0 then i have to display the Coverage. Else if stock of previous day has a value > 0 then the Coverage is forced to be displayed as 0.

Example for the bar chart (x-axis displays column date and y-axis displays column Coverage😞

For x-axis date value 05/01/2015, the y-axis value must be 33 as the stock of the previous day 04/01/2015 is 0

For x-axis date value 04/01/2015, the y-axis value must be forced to 0 as the stock of the previous day 03/01/2015 is 18 (i.e. greater than 0)

For x-axis date value 03/01/2015, the y-axis value must be 45 as the stock of the previous day 02/01/2015 is 0

For x-axis date value 02/01/2015, the y-axis value must be forced to 0 as the stock of the previous day 01/01/2015 is 44 (i.e. greater than 0)

For x-axis date value 01/01/2015, the y-axis value must be 10 as there is no previous stock value

On my bar chart properties, I have set the following properties:

Column Date is set as the Dimension

For expression, i set it as :

if (

Sum({$<Date={">=$(=max(Date(Date-45)))<=$(=max(Date)))"} >}Aggr(sum({$<Date={">=$(=max(Date(Date-45)))<=$(=max(Date)))"}QTY_STOCK),Date-1)) > 0,

Coverage)

Grateful if you could please help

Thanks in advance

Re: Bar chart display based on previous day comparison

sunny_talwar — Tue, 12 May 2015 10:06:39 GMT

Because you have date as your dimension, set analysis won't work the way you are expecting it work and you will need to use above function here to get the desired result in your case.

Re: Bar chart display based on previous day comparison

Tue, 12 May 2015 10:17:12 GMT

could you help me to write the set analysis with the above function?

Re: Bar chart display based on previous day comparison

sunny_talwar — Tue, 12 May 2015 10:19:33 GMT

Sure thing. I am working on it

Re: Bar chart display based on previous day comparison

sunny_talwar — Tue, 12 May 2015 10:27:16 GMT

Try this expression:

=If(Alt(Above(Sum(Stock)), 0) = 0, Sum(Coverage), 0)

Also find attached a sample application for your reference.

Best,

Sunny

Re: Bar chart display based on previous day comparison

Tue, 12 May 2015 10:46:26 GMT

Thanks lots for your answer..

super it looks good.. i will implement it to display 45 days of data in the barchart

Re: Bar chart display based on previous day comparison

sunny_talwar — Tue, 12 May 2015 10:48:00 GMT

No problem . I am glad I was able to help.

Best,

Sunny