Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
See attached example.
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
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