topic Re: Set Analysis Expression Help in App Development

Set Analysis Expression Help

robertsb41 — Sat, 16 Nov 2024 01:57:41 GMT

(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!

Re: Set Analysis Expression Help

robertsb41 — Tue, 07 Jul 2020 15:28:24 GMT

count ({<ABC_Est_Cost={"=(ABC_Est_Cost) * (MU_Perc) + (ABC_Est_Cost) < Low_Comp_Bid"}>} ABC_Est_Cost)

This was my best effort but it only works if I replace MU_Perc and ABC_Est_Cost with numbers. It always returns 0 when I try to use the field names.

Re: Set Analysis Expression Help

Kushal_Chawda — Tue, 07 Jul 2020 15:34:29 GMT

may be this

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

Re: Set Analysis Expression Help

robertsb41 — Tue, 07 Jul 2020 15:48:39 GMT

That still returns 0.

This works:

count ({<ABC_Est_Cost={"=(sum(ABC_Est_Cost) * sum(1.01)) + sum(1000) < sum(Low_Comp_Bid)"}>} ABC_Est_Cost)

But as soon as I replace 1.01 with MU_Perc and 1000 with ABC_Est_Cost it returns 0 again.

Re: Set Analysis Expression Help

Kushal_Chawda — Tue, 07 Jul 2020 16:15:29 GMT

Is your fields MU_Perc & ABC_Est_Cost are in number format? Would you be able to share example?

Re: Set Analysis Expression Help

robertsb41 — Tue, 07 Jul 2020 16:19:02 GMT

MU_Perc (1-23 values) is a percentage and ABC_Est_Cost is a number.

Re: Set Analysis Expression Help

Kushal_Chawda — Tue, 07 Jul 2020 16:23:15 GMT

Without looking at the data it will be difficult to tell what will be the issue. Is it possible to share sample data?

Re: Set Analysis Expression Help

robertsb41 — Tue, 07 Jul 2020 16:28:59 GMT

This is all dummy data so no worries.

Re: Set Analysis Expression Help

robertsb41 — Tue, 07 Jul 2020 16:31:18 GMT

Sorry, its the markup analysis sheet and # of jobs won column. That number should go down as the Markup% goes up.

Re: Set Analysis Expression Help

Kushal_Chawda — Tue, 07 Jul 2020 16:42:57 GMT

Can you tell me what could be the expected output with one example?

Re: Set Analysis Expression Help

robertsb41 — Tue, 07 Jul 2020 16:55:23 GMT

1% Markup should return a value of 53.

Re: Set Analysis Expression Help

robertsb41 — Tue, 07 Jul 2020 17:03:51 GMT

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)

Re: Set Analysis Expression Help

Kushal_Chawda — Tue, 07 Jul 2020 18:40:57 GMT

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))

Re: Set Analysis Expression Help

robertsb41 — Tue, 07 Jul 2020 19:30:44 GMT

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!

Re: Set Analysis Expression Help

Kushal_Chawda — Tue, 07 Jul 2020 22:04:44 GMT

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"

Re: Set Analysis Expression Help

robertsb41 — Wed, 08 Jul 2020 14:33:13 GMT

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?

Re: Set Analysis Expression Help

Kushal_Chawda — Wed, 08 Jul 2020 20:43:53 GMT

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

Re: Set Analysis Expression Help

robertsb41 — Wed, 08 Jul 2020 20:56:38 GMT

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))