topic Re: Date Island Issue in QlikView

Date Island Issue

qliksus — Tue, 21 Feb 2017 21:35:48 GMT

Hi All,

I have set of data on which i want to see the sales by Month , for the first 6 months would be Sum of Sales and rest of 6 months output would be sum of last 3 months.

Input Data:

Date	Sale
31/01/2016	100
29/02/2016	110
31/03/2016	120
30/04/2016	130
31/05/2016	140
30/06/2016	150
31/07/2016	160
31/08/2016	170
30/09/2016	180
31/10/2016	190
30/11/2016	200
31/12/2016	210

OutPut:

Report_Date	Output
31/01/2016	100
29/02/2016	110
31/03/2016	120
30/04/2016	130
31/05/2016	140
30/06/2016	150
31/07/2016	420
31/08/2016	450
30/09/2016	480
31/10/2016	510
30/11/2016	540
31/12/2016	570

I have tried using Date Island but no luck. Can anyone help me resolve it?

Re: Date Island Issue

sunny_talwar — Tue, 21 Feb 2017 21:44:58 GMT

May be this:

If(RowNo() <= 6, Sum(Sale), RangeSum(Above(Sum(Sale), 1, 3)))

Re: Date Island Issue

qliksus — Tue, 21 Feb 2017 21:57:46 GMT

Many Thanks Sunny, Range sum won't work here as the base line data is stored in little bit different way. I want to achieve the same using Date Island in Set analysis. let me show you the underline data as

Date	Category	Sale
31/01/2016	A	100
31/01/2016	B	310
29/02/2016	A	110
29/02/2016	B	320
31/03/2016	A	120
31/03/2016	B	330
30/04/2016	A	130
30/04/2016	B	340
31/05/2016	A	140
31/05/2016	B	350
30/06/2016	A	150
30/06/2016	B	370
31/07/2016	A	160
31/08/2016	A	170
30/09/2016	A	180
31/10/2016	A	190
30/11/2016	A	200
31/12/2016	A	210

We have 2 categories data as A & B, whereas A have all 12 month data and B have only 6 Month data. Basically we need to populate B's 6 month of data based on A's last 6 Month data. Hope it make more clear now

Re: Date Island Issue

sunny_talwar — Wed, 22 Feb 2017 09:15:35 GMT

What is the expected output here?

Re: Date Island Issue

qliksus — Wed, 22 Feb 2017 09:52:28 GMT

Hi Sunny,

Expected output is

31/01/2016

100

31/01/2016

310

29/02/2016

110

29/02/2016

320

31/03/2016

120

31/03/2016

330

30/04/2016

130

30/04/2016

340

31/05/2016

140

31/05/2016

350

30/06/2016

150

30/06/2016

370

31/07/2016

160

31/08/2016

170

30/09/2016

180

31/10/2016

190

30/11/2016

200

31/12/2016

210

31/07/2016

420

31/08/2016

450

30/09/2016

480

31/10/2016

510

30/11/2016

540

31/12/2016

570

Re: Date Island Issue

sunny_talwar — Wed, 22 Feb 2017 10:06:43 GMT

Are there only 2 categories? what happens when there is a third category? 2 of them have values and 1 don't or 1 of them have values and 2 don't. I am confused how this is expanded in your actual dashboard.

Re: Date Island Issue

qliksus — Wed, 22 Feb 2017 10:12:19 GMT

Hi,

Always there have to be only 2 categories, A should have 12 Month data and B have partial. B's missing month value should be derived based on A's value [ Sum of last 3 Month].

e:g: In case of July-2016, B's value would be sum of A's [Jun-2016 , May-2016, Apr-2016].

Hope you are clear now.

Re: Date Island Issue

sunny_talwar — Fri, 24 Feb 2017 12:17:03 GMT

Try this:

=Aggr(If(Sum(Sale) = 0, RangeSum(Above(Aggr(Alt(Above(Sum(Sale)), Below(Sum(Sale))), Date, Category), 1, 3)), Sum(Sale)), Category, Date)