Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
any ideas?
anyone?
Hello,
Actually your explanation is toooooooooo big. Can you explain with sample data and required output?
Regards,
Prashant Sangle