topic Complex set analysis within a if statement........ in QlikView

Complex set analysis within a if statement........

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi Guys,

I'm doing a set analysis within an If statement, It works perfect when i select one value from a list box and it shows it perfect in a single row, but when i wanna see all the values, they have the same value as the result.

Any help, top of your head? I know i'll probable need to show some data and what I've done, I'm working on a dummy data demo.

Thanks

And long time no chat.

Re: Complex set analysis within a if statement........

oscar_ortiz — Tue, 11 Jul 2017 16:04:22 GMT

You're going to need to provide some more information.

What is the actual expression that you are having issues with?

Re: Complex set analysis within a if statement........

Anil_Babu_Samineni — Tue, 11 Jul 2017 16:04:46 GMT

Looks like strange issue. What is the condition and where do you use ??

Re: Complex set analysis within a if statement........

Anonymous — Tue, 11 Jul 2017 16:07:03 GMT

Or if i do it this way, it shows me the value if below "0" , I want it to show me 0, if below and the value if higher than 0.

=if((sum({<VER={"OPEN"}>}EPRC)

sum({<VER={"ACT"}>}EPRC)

sum({<VER={"OPEN"}>}EPRC)<0),'0',sum({<VER={"OPEN"}>}EPRC)

sum({<VER={"ACT"}>}EPRC)

sum({<VER={"PLAN"}>}EPRC))

Re: Complex set analysis within a if statement........

Anonymous — Tue, 11 Jul 2017 16:07:35 GMT

That's what I was afraid of lol.

Re: Complex set analysis within a if statement........

Anonymous — Tue, 11 Jul 2017 16:08:14 GMT

Pivot table

Re: Complex set analysis within a if statement........

Anil_Babu_Samineni — Tue, 11 Jul 2017 16:12:17 GMT

May be this? Looks like mis place the close parenthesis

=if((sum({<VER={"OPEN"}>}EPRC) + sum({<VER={"ACT"}>}EPRC) - sum({<VER={"OPEN"}>}EPRC))<0,'0',(sum({<VER={"OPEN"}>}EPRC)+sum({<VER={"ACT"}>}EPRC)-sum({<VER={"PLAN"}>}EPRC))

(sum({<VER={"OPEN"}, ERPC = {"Sum(ERPC) < 0"}>}EPRC)+sum({<VER={"ACT"}, ERPC = {"Sum(ERPC) < = 0"}>}EPRC)-sum({<VER={"PLAN"}, ERPC = {"Sum(ERPC) < 0"}>}EPRC))

Re: Complex set analysis within a if statement........

Anonymous — Tue, 11 Jul 2017 16:17:34 GMT

None of them worked.

I know it's hard to do without all the data and to see what I've done so far.

Re: Complex set analysis within a if statement........

vishsaggi — Tue, 11 Jul 2017 16:26:16 GMT

Try this?

= if( RangeSum(sum({<VER={"OPEN"}>}EPRC),sum({<VER={"ACT"}>}EPRC)) - sum({<VER={"OPEN"}>}EPRC) < 0, 0,

RangeSum(sum({<VER={"OPEN"}>}EPRC),sum({<VER={"ACT"}>}EPRC)) - sum({<VER={"PLAN"}>}EPRC))

Re: Complex set analysis within a if statement........

Anonymous — Tue, 11 Jul 2017 16:38:43 GMT

No, That's giving me the same results as mine.

I want

The expression to = '0' if I've reached my target (), and show the value needed if I haven't reached my target.

Re: Complex set analysis within a if statement........

vishsaggi — Tue, 11 Jul 2017 16:52:09 GMT

Time to share some sample data with expected output?

Re: Complex set analysis within a if statement........

sunny_talwar — Tue, 11 Jul 2017 16:59:34 GMT

May be this

=RangeMax(0, Sum({<VER = {"OPEN", "ACT"}>}EPRC) - Sum({<VER = {"PLAN"}>}EPRC))

Re: Complex set analysis within a if statement........

Anil_Babu_Samineni — Wed, 12 Jul 2017 05:09:58 GMT

What are you getting when you use this alone?

(sum({<VER={"OPEN"}>}EPRC) + sum({<VER={"ACT"}>}EPRC)) - sum({<VER={"OPEN"}>}EPRC)

Re: Complex set analysis within a if statement........

Anonymous — Wed, 12 Jul 2017 12:51:22 GMT

No, still didn't work

Re: Complex set analysis within a if statement........

sunny_talwar — Wed, 12 Jul 2017 12:53:24 GMT

Sample would be helpful as others also mentioned

Re: Complex set analysis within a if statement........

Anonymous — Wed, 12 Jul 2017 13:05:07 GMT

Ok, I'll give you something soon Sunny.

Re: Complex set analysis within a if statement........

Anonymous — Thu, 13 Jul 2017 09:05:07 GMT

Got it working

=if(num(SUM({<VER={$(vPreviousMonth)},YR={$(vCurrentYear)}>}EPRC)/1000,'#,##0')

(sum({<VER={"OPEN"}>}EPRC)/1000

sum({<VER={"ACT"}>}EPRC)/1000)<0,0,num(SUM({<VER={$(vPreviousMonth)},YR={$(vCurrentYear)}>}EPRC)/1000,'#,##0')

(sum({<VER={"OPEN"}>}EPRC)/1000

sum({<VER={"ACT"}>}EPRC)/1000))