topic Re: Conditional within another conditional set analysis in QlikView

Conditional within another conditional set analysis

Anonymous — Fri, 18 Mar 2016 11:06:56 GMT

Hi People,

I have set analysis like this:

sum( {$< Year = {$(=only(left(Year-Month,4)))}, ID_Month={'<=2'}>}Amount

And this is working.

But i want to have one more conditional in ID_Month. I want to do this conditional working only in 2016 (last reporting year).

So in 2015 ID_Month could be all, from 1 to 12 but in 2016 i want to have only 1 and 2 month (less then 2)

Please help me Guys,

Thank you in Advance

Jacek Antek

Re: Conditional within another conditional set analysis

Chanty4u — Fri, 18 Mar 2016 11:09:54 GMT

chk dis

P() and E() in set analysis expression

Re: Conditional within another conditional set analysis

sunny_talwar — Fri, 18 Mar 2016 11:11:36 GMT

May be like this:

Sum({$<Year = {$(=Only(Left(Year-Month,4)))},ID_Month={$(=If(Left(Year-Month, 4) = 2016, Chr(39) & '<=2' & Chr(39), Chr(39) & '*' & Chr(39)))}>} Amount)

Re: Conditional within another conditional set analysis

avinashelite — Fri, 18 Mar 2016 11:18:10 GMT

I think you could add a if statement around it

if(left(Year-Month,4)<2016, sum( {$< Year = {$(=only(left(Year-Month,4)))}, Amount),

sum( {$< Year = {$(=only(left(Year-Month,4)))}, ID_Month={'<=2'}>}Amount)

)

Re: Conditional within another conditional set analysis

Anonymous — Fri, 18 Mar 2016 13:06:34 GMT

Thank You for your swift answer.

I prefer SunnyT solution but Avinash R also has right.

Sunny T

Sum({$<Year = {$(=Only(Left(Year-Month,4)))}, D_Month={$(=If(Left(Year-Month, 4) = 2016, Chr(39) & '<=2' & Chr(39), Chr(39) & '*' & Chr(39)))}>} Amount)

Above is working without these strange and difficult for me CHr(39) (what is this BTW? )

Now i have to implement instead Year-Month Column - my viariable - vDateClosed : 2016-02

D_Month={$(=If(Left(vDateClosed, 4) = 2016 it doesnt work.

How should i write this?

thanks in advance for help!

Jacek Antek

Re: Conditional within another conditional set analysis

sunny_talwar — Fri, 18 Mar 2016 13:08:04 GMT

Chr(39) is another way of adding a single quote

Re: Conditional within another conditional set analysis

Anonymous — Fri, 18 Mar 2016 13:14:46 GMT

Ok thank you. it is not neccessary here.

So what with variable and left from it?

Re: Conditional within another conditional set analysis

sunny_talwar — Fri, 18 Mar 2016 13:15:22 GMT

I am difficultly interpreting your new requirement, can you elaborate?

Re: Conditional within another conditional set analysis

sunny_talwar — Fri, 18 Mar 2016 13:15:57 GMT

Can you re-explain the new requirement?

Re: Conditional within another conditional set analysis

Anonymous — Fri, 18 Mar 2016 13:19:12 GMT

If(Left(Year-Month, 4) = 2016 is working but field Year Month is a column from Table.

vDateClosed is does not working in form as above - it is variable.

I suppose that QV differently see Variable and Column Header in set analysis...

Re: Conditional within another conditional set analysis

sunny_talwar — Fri, 18 Mar 2016 13:25:39 GMT

Not sure , but what about this:

D_Month={$(=If(Left($(vDateClosed), 4) = 2016

Re: Conditional within another conditional set analysis

Anonymous — Fri, 18 Mar 2016 13:36:05 GMT

Unfortunately, it is not working...:/

Re: Conditional within another conditional set analysis

sunny_talwar — Fri, 18 Mar 2016 13:37:35 GMT

When you put vDateClosed in a text box object, what do you see?

Re: Conditional within another conditional set analysis

Anonymous — Fri, 18 Mar 2016 13:46:20 GMT

I see:

So even if i wrote in tex box : left($(vDateClosed),4) i have seen 2016.

Re: Conditional within another conditional set analysis

sunny_talwar — Fri, 18 Mar 2016 13:51:41 GMT

What about this in text box:

=If(Left(vDateClosed, 4) = 2016, Chr(39) & '<=2' & Chr(39), Chr(39) & '*' & Chr(39))

Should give you '<=2' when you have 201602 in vDateClosed. for any other year you should see '*'

Re: Conditional within another conditional set analysis

Anonymous — Fri, 18 Mar 2016 14:02:43 GMT

yes it is working:

Maybe this is a problem within chart bar - im trying to implement this expression there.

Re: Conditional within another conditional set analysis

sunny_talwar — Fri, 18 Mar 2016 14:06:17 GMT

So this isn't working?

Sum({$<Year = {$(=Only(Left(Year-Month,4)))}, D_Month={$(=If(Left(vDateClosed, 4) = 2016, Chr(39) & '<=2' & Chr(39), Chr(39) & '*' & Chr(39)))}>} Amount)

Is there a possibility to share a sample?

Re: Conditional within another conditional set analysis

Anonymous — Fri, 18 Mar 2016 14:16:23 GMT

Yes this isn't working.

It will a problem with sharing this - data are only for company use and i can not share applications ;/

hmm i can manually insted of Left(vDateClosed,4) write there Year = '2016' but it is not satisfying me

Re: Conditional within another conditional set analysis

sunny_talwar — Fri, 18 Mar 2016 14:18:03 GMT

It's strange it isn't working, can you share a screenshot of the output? What is the error you are seeing?

Re: Conditional within another conditional set analysis

Anonymous — Fri, 18 Mar 2016 14:25:56 GMT

Ok,

my expression is more advanced then in example, i didnt want to bother yoy with this.

But still problem is the same.

When i have:

num(Sum({$<MO_ID={1}, Year= {$(=only('20'&(Left([Year-Month],2))))}, Month=,
ID_Month={"$(=if(left(vDateClosed,4)='2016','<=$(=right(vDateClosed,1)*1)','<=$(=Max({1} ID_Month))'))"}>}Amount)/1000,'# ##0.00') it is not working.

When i have :

num(Sum({$<MO_ID={1}, Year= {$(=only('20'&(Left([Year-Month],2))))}, Month=,
ID_Month={"$(=if(Year='2016','<=$(=right(vDateClosed,1)*1)','<=$(=Max({1} ID_Month))'))"}>}Amount)/1000,'# ##0.00')

It is working, it is strange. Only different is in writing variable instead of column header.