Conditional within another conditional set analysis

[Anonymous]

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

[Anonymous]

Version with not working expression with variable in 2015 and 2016 is the same (Wn - dark bar is only in Jan and Feb):

Working expression with Year = '2016'  insted of variable in 2015 is showing all months, in 2016 is showing only Jan and Feb.

[sunny_talwar · Accepted solution]

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.

[Anonymous]

Ok i now the solution - it is simple:

ID_Miesiąc={"$(=if(Year=left(vDateClosed,4)

and this is working. Thank You for all your help!

You have right, it will be a problem in 201610. Hmm i should add there zeros or do something else

this is not working :

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

it is very very strange.

[Anonymous]

I have unfortunately one more problem.

When i have cumulative sums on bars i have expression:

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

And i have checked the option full cumulative.

Our expression is not working in this case,m

any suggestions?

Please Help,

Jacek Antek

[Anonymous]

I have unfortunately one more problem.

When i have cumulative sums on bars i have expression:

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

And i have checked the option full cumulative.

Our expression is not working in this case,m

any suggestions?

Please Help,

Jacek Antek

[sunny_talwar]

I would rather do the accumulation using RangeSum(Above()) function rather than using the inbuilt 'Accumulation' by QlikView.

[Anonymous]

Thanks Sunny T for your answer!

Why do you prefer this function instead of inbuild QV 'Accumulation'?

[sunny_talwar]

Because it gives me the flexibility to tweak around things. Whereas with the inbuilt function, it is definitely fast, but you get what's available.

[avinashelite]

check this thread for the detailed information

Calculating rolling n-period totals, averages or other aggregations