Set Analysis Expression Help

[robertsb41]

(Sum([ABC_Est_Cost]*[MU_Perc]) + Sum([ABC_Est_Cost]))

This expression works great but I want to only return the sum of this expression when:

([ABC_Est_Cost]*[MU_Perc]) + Sum([ABC_Est_Cost]) is < [Low_Comp_Bid]

Any help is appreciated!

[robertsb41]

1% Markup should return a value of 53.

[robertsb41]

This works and would represent the 1% Markup but I need to use MU_Perc instead of .01 so that the other rows calculate properly.

count ({<ABC_Est_Cost={"=(ABC_Est_Cost) * (.01) + (ABC_Est_Cost) < sum(Low_Comp_Bid)"}>} ABC_Est_Cost)

[Kushal_Chawda · Accepted solution]

try this

count(aggr(if(sum(ABC_Est_Cost*MU_Perc)+sum(ABC_Est_Cost)<sum(Low_Comp_Bid),ABC_Est_Cost),ABC_Est_Cost,MU_Perc))

[robertsb41]

You are a genius!  Do you know of any literature on these types of expressions?  I'd like to learn the logic that you used to write that.  Thank you!

[Kushal_Chawda]

Firstly, I am not genius 😊. I am still learning and still waiting to match the other experts over here. Well, best place to learn is this awesome community and also practice. Follow all the posts and answers provided by experts here which will help you to understand lot many new techniques. Try to understand the concepts by reading blogs for many techniques. Like for my answer you many want to learn how aggr function works then search for aggr function for qlik in google and you will find lot many resources. But don't forget to practice. Somebody said it right "Practice makes man perfect"

[robertsb41]

I feel like I'm pushing me luck asking for more help but I'm stuck again.

sum(aggr(if(Sum(ABC_Est_Cost*MU_Perc)+Sum(ABC_Est_Cost)<sum(Low_Comp_Bid),ABC_Est_Cost*MU_Perc / (2-1)),ABC_Est_Cost,MU_Perc))

The above aggr works as expected but when I replace 2-1 with Low_Comp_Bid - ABC_Est_Cost it returns incorrect results.

Any ideas on this one?

[Kushal_Chawda]

what should be the result? I am getting 11 for 1%

[robertsb41]

1% should be 15.2 but I think I got it with the below expression:

sum(aggr(if(Sum(ABC_Est_Cost*MU_Perc)+Sum(ABC_Est_Cost)<sum(Low_Comp_Bid),ABC_Est_Cost*MU_Perc),ABC_Est_Cost,MU_Perc)) /
sum(aggr(if(Sum(ABC_Est_Cost*MU_Perc)+Sum(ABC_Est_Cost)<sum(Low_Comp_Bid),Low_Comp_Bid - ABC_Est_Cost),ABC_Est_Cost,MU_Perc))