Solved: Conditional within another conditional set analysi... - Qlik Community

Anonymous · ‎2016-03-18

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

sunny_talwar · ‎2016-03-18

Couple of things:

1) Try this expression:

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

2) I don't like this part of your expression

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

What happens when its 201610? Right will only pick 0 (because of your right function). There are better ways to handle this, I think you should reconsider your approach and use dates instead of text's with right and left.

View solution in original post

Chanty4u · ‎2016-03-18

chk dis

P() and E() in set analysis expression

sunny_talwar · ‎2016-03-18

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)

avinashelite · ‎2016-03-18

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)

)

Anonymous · ‎2016-03-18

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

sunny_talwar · ‎2016-03-18

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

Anonymous · ‎2016-03-18

Ok thank you. it is not neccessary here.

So what with variable and left from it?

sunny_talwar · ‎2016-03-18

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

sunny_talwar · ‎2016-03-18

Can you re-explain the new requirement?

Anonymous · ‎2016-03-18

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

Conditional within another conditional set analysis