Data presentation

[santhosh_k_n]

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

                    or

Measeure1=Measure2 if Measeure2 is available

                    or

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

2. Measure6= measure6 on To date

          or

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

Please guide

[santhosh_k_n]

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

[Gysbert_Wassenaar · Accepted solution]

See attached example.

[santhosh_k_n]

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

[santhosh_k_n]

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