Need help in writing a complex and tricky logic

[Anonymous]

Hi,

I have a table like below:

Calc_Direct_Margin	DM	MonYear
-21703.86528	Wave3	Nov 2016
20422.08753	Wave4	Nov 2016
103283.0161	Wave5	Nov 2016
199305.2114	Wave3	Sep 2016
211410.4014	Wave3	Oct 2016
237088.6261	Wave1	Nov 2016
267382.7863	Wave3	Jul 2016
319106.4065	Wave3-Target	Jul 2016
323192.6291	Wave3	Aug 2016
406481.3737	Wave4	Oct 2016
416960.1069	Wave2	Nov 2016
576796.3192	Wave4-Target	Jul 2016
617509.4746	Wave5	Sep 2016
650279.9086	Wave5	Jul 2016
651818.9923	Wave4	Aug 2016
701478.7511	Wave5-Target	Jul 2016
737604.8498	Wave4	Sep 2016

I have to create a line chart with MonYear and DM as two dimensions. Then the Sum(Calc_Direct_Margin) as the expression.

Here the trickier part is any DM with the name as "Target" has value for only one MonYear. Example, Wave3-Target, Wave4-Target and Wave5-Target has value only for MonYear 'Jul 2016' and does not have any value for rest of the MonYear values.

So the requirement is for only those Target Waves, i have to accumulate the value for the rest of the MonYear. Example: If Wave3-Target value is 319106.4, then for Aug 2016 it should be 319106.4+319106.4, for Sep 2016 it should be319106.4+319106.4+319106.4 and so on (Accumulation).

But for the DM without Target on their names, the expression should be a simple sum(Calc_Direct_Margin).

Can someone please help me on this logic?

[maksim_senin · Accepted solution]

Hi Karthick,

A QVW sample would help, but in genral you can do the following:

1. create/load a table with unique months you need to fill the Calc_Direct_Margin value for. Say its name is Target

2. than:

Left Join(Target)

load

DM,

Calc_Direct_Margin

Resident <Your Table Name>

Where DM like '*-Target'

This would spread target values to all monthes you'll have in the Target table.

Best regards,

Maxim

[Anonymous]

Thank You so much Maxim Senin. This is what i exactly looking for. So the concept here is, if there is no matching field between tables then automatically cross join will happen here?

[maksim_senin]

The concept here is more about scripting itself - it allows you to prepare your data as you want. Just learn, play and experement.

Best regards,

Maxim