Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
karthick01
Contributor III
Contributor III

Need help in writing a complex and tricky logic

Hi,

I have a table like below:

Calc_Direct_MarginDMMonYear
-21703.86528Wave3Nov 2016
20422.08753Wave4Nov 2016
103283.0161Wave5Nov 2016
199305.2114Wave3Sep 2016
211410.4014Wave3Oct 2016
237088.6261Wave1Nov 2016
267382.7863Wave3Jul 2016
319106.4065Wave3-TargetJul 2016
323192.6291Wave3Aug 2016
406481.3737Wave4Oct 2016
416960.1069Wave2Nov 2016
576796.3192Wave4-TargetJul 2016
617509.4746Wave5Sep 2016
650279.9086Wave5Jul 2016
651818.9923Wave4Aug 2016
701478.7511Wave5-TargetJul 2016
737604.8498Wave4Sep 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?

1 Solution

Accepted Solutions
maxim_senin
Partner
Partner

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

View solution in original post

3 Replies
maxim_senin
Partner
Partner

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

View solution in original post

karthick01
Contributor III
Contributor III
Author

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?

maxim_senin
Partner
Partner

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