Measure Total function not working with field expression

[roisolberg]

Hi,

background:

the table i have contains 3 objects that are connected via associative relationship by the Opportunity ID (Opportunity, Program, Potential)

*Opportunity is the main object, it has 1-1 relationship with the Program and can have multiple Potentials (1-*)

the table granularity is at the Potential level, so every opportunity can have multiple lines

and i use the Measure total function in the table to (Auto mode, so it wont duplicate count the opportunity fields)

until here everything is ok and everything was calculating properly 

now i got the following request:

basically i need to check for a logic on the opportunity and flag it

for each row of the flag i need represent some of the fields under type A and some under type B (The same type field)

if it is A, i sum the Opportunity fields (Program & Potential = 0 )

if it is B, i sum the Potential and Program fields (Opportunity=0)

i handled it like so:

for each flagged opportunity i've made a self join in order to duplicate the line

once with type A and once with type B

then Summed the Opportunity fields and gave 0 (in the script) to the potential fields for each record of the type=A

and i tried to sum the Program/Potential fields(only if type =B)

i dont want to duplicate the Potential line, so i've tried to Sum it inside the Qlik table 

using the expression : If(Type='A', Sum(Potential_Amount_USD) ,0)

but the total of the table shows 0

see pic, i am trying to move for the original table (Top) to the new table below

notice, that sum fields are calculated to type A and the rest for type B

the yellow lines are the opportunity self join with type=b, the duplication is from the potential lines 

and the following pic is from the Qlik table:

1 field was changed in script, and working well

another 1 using an if expression (Total not working)

and the last one is like a control field for better understanding

So my questions to the amazing people that got till here is:

1.what should i do with the totals function? (sum does not work as it multiplies the opportunity values * # of potential rows

2. can you see a better way to do what I'm trying to do?

i really really hope I've managed to explain myself properly 

Thanks for reading 🙂

Roi

[roisolberg]

any ideas?

[roisolberg]

anyone?

[PrashantSangle]

Hello,

Actually your explanation is toooooooooo big. Can you explain with sample data and required output?

Regards,

Prashant Sangle