Hi,
I have a chart that I am trying to create which is about customers and their migration over two chosen periods.
Part of this involves me having to calculate a value from one of the chosen periods but using an attribute from the other chosen period.
To calculate the original values I use data islands for the period selections and I only allow customer IDs that occur in both periods. To do this I use set analysis as can be seen in my attached document in the "From Value * Multipliers" (using the Date_From filter) and "To Value * Multipliers" (using the Date_To filter). These two fields calculate the result using both the Value and 2 Multipliers from the same period, but what I need to do is calculate the result using the Value and Multiplier_1 from one period but the Multiplier_2 from the other period.
The IDs in the real QVW are not unique. The two multipliers will be the same for each unique ID but the value will be different.
The real calculation is a lot more complicated, and I have tried to rework the calculation so that you can make two different SUMs using different periods but I have found it impossible because you have to use the multipliers together in parts of the equation calculated at row level. Also, the dimension of the chart will not be at ID level, so you can't make another expression with the Multiplier_2 in it and the include the result of the expression in the calculation.
I thought that if I could apply the set analysis for Date_From in the calculation for Date_To using some kind of aggregation like Max, Only, Mode or something similar, since per ID the returned value will always be the same, that maybe I could do it but I can't figure out a way to do this.
If anyone has any ideas how to do this please let me know. Your help is greatly appreciated.
