topic Re: Data presentation in QlikView

Data presentation

santhosh_k_n — Thu, 04 Dec 2014 04:47:33 GMT

hi all,

I am having data as follows,

DIm1	Dim2	Product	Date	Measere1	Measure2	Measure3	Measure4	Measure5	Measure6
D1	C1	p1	1/11/2014 0:00	100	0	35	30	25	110
D1	C1	p1	1/12/2014 0:00	0	110	25	0	0	135
D1	C1	p1	1/13/2014 0:00	0	135	100	30	90	145
D1	C1	p1	1/16/2014 0:00	0	0	0	0	0	0
D1	C1	p1	1/17/2014 0:00	0	145	50	24	65	130
D1	C1	p1	1/18/2014 0:00	0	130	0	10	0	130
D1	C1	p1	1/19/2014 0:00	0	130	32	4	45	117
D1	C1	p1	1/20/2014 0:00	0	117	150	0	50	217
D1	C1	p1	1/21/2014 0:00	0	217	200	43	55	362
D1	C1	p1	1/24/2014 0:00	0	0	0	0	0	0
D1	C1	p1	1/29/2014 0:00	0	362	0	10	100	262
D1	C1	p1	1/30/2014 0:00	0	262	90	80	45	307
D1	C1	p1	1/31/2014 0:00	0	307	50	60	44	313
D1	C1	p1	2/1/2014 0:00	0	313	40	0	67	286
D1	C1	p1	2/2/2014 0:00	0	286	30	0	23	293

Where

1. Measure2 will be zero if measure1 is available

2. Data gets generated from the day Measure1 data is entered

3. Measure2=Measure6 from next day of the Measure1 entered

4. measure6 is calculated based on the measuer1 to measure5

Now when am applying filters on the above data and present in the report the result should be as follows,

1. From date: 01/01/204 to 30/01/2014

DIm1	Dim2	Product	Measere1	Measure3	Measure4	Measure5	Measure6
D1	C1	p1	0	0	0	0	0

2. From date 11/01/2014 to 30/01/2014

DIm1	Dim2	Product	Measere1	Measure3	Measure4	Measure5	Measure6
D1	C1	p1	100	682	231	475	307

3. from date: 15/Nov/2014 to 25/Nov/2014

DIm1	Dim2	Product	Measere1	Measure3	Measure4	Measure5	Measure6
D1	C1	p1	145	432	81	215	362

The report logic for Measure1 and Measure6 are as follows,

1. Measure1 = Measure1 is measure1 is available

Measeure1=Measure2 if Measeure2 is available

Measure1 =measure6 for the last date which is less than from date

2. Measure6= measure6 on To date

Measure6=Measure6 of the last max date which is less than To date

Please guide

Re: Data presentation

santhosh_k_n — Fri, 26 Dec 2014 04:56:15 GMT

Hi all,

I am able to present this data as required if i duplicate the Measure2 and Measure6 for each date(for missing dated in the table.) but this is increasing the data load and QVD size which is not suggestible.

Can you please guide on this for any alternate solution.

Regards,

Santhosh N

Re: Data presentation

Gysbert_Wassenaar — Fri, 26 Dec 2014 08:24:22 GMT

See attached example.

Re: Data presentation

santhosh_k_n — Fri, 26 Dec 2014 09:03:54 GMT

Hi Gysbert,

Thanks for the solution but I had one more issue hear.

The Data filter am using in the report is another field from a Calender related table and I am filtering the data in Transactions by mapping Date filed to the Date field in the Calender table.

Now in this scenario which date field i need to use in the expression.

BR,

Santhosh N

Re: Data presentation

santhosh_k_n — Tue, 30 Dec 2014 09:43:11 GMT

Hi Gysbert,

In the report i posted the 3rd case, where from date is 15th Jan which has no data, but on 15th the Measure 1 in report should be equal to the previous available date, that is 13th Jan's Measure6, can you please guide how to implement this.

Regards,

Santhosh