Alternative for Using MonthEnd in If Statement

[cbaqir]

I have an urgent need to get this fixed ASAP so help is greatly appreciated!

Awhile back, I posted https://community.qlik.com/message/831371?et=watches.email.thread#831371 and received some great info from on how to Use MonthEnd in If Statement. Unfortunately, I have discovered an issue with using this expression:

Expression (With no accumulation)

If(Sum(ACT_AMT) > 0 or Sum(FCST_AMT) > 0,

RangeSum(Above(TOTAL If(FISCAL_MONTH_YEAR <= Monthend(Today()), Sum(ACT_AMT), Sum(FCST_AMT)), 0, RowNo())))

When I look at the graph today, I don't see any x-axis date for July 2015. I assume that this because the expression is using today's date instead of looking at the data to see when the most recent TRX_DATE for ACT_AMT is. Our ACT_AMT takes a couple of weeks to post so it is not unusual for it to be 8/12/15 but not yet have data for July 2015.

The intent of the graph is to show ACT_AMT for historical information and FCST_AMT for future information... but this needs to be based on the date of ACT_AMT in the data as opposed to the current date.

Unfortunately, I cannot get the sample app to replicate the issue that I am seeing in my actual dashboard so my assumption of the issue might not be correct.

Live Dashboard:

Thanks to all you wonderful people!

[yura_ratu · Accepted solution]

Hi Cassandra,

Please try this expression

If(Sum(ACT_AMT) > 0 or Sum(FCST_AMT) > 0,

RangeSum(Above(TOTAL If(FISCAL_MONTH_YEAR <= Monthend(Max({1<ACT_AMT={">0"}>}TRX_DATE)), Sum(ACT_AMT), Sum(FCST_AMT)), 0, RowNo())))

[sunny_talwar]

I believe that the solution provided by Yurii‌ is certainly doing what you wanted. Here I did a comparison of what my expression was doing and what his is doing:

Expression with 2 is what Yurii offered (and 1 was what I initially provided). Here we see that for the year where Actual is available you only see amount accumulated for actual amount and starting next period the accumulation start to happen on forecast data.

My only concern here would be the use of 1 in the expression (which is forcing it to not change based on selections (Monthend(Max({1<ACT_AMT={">0"}>}TRX_DATE))). I would try to make some selections and see if the results are what you thought you wanted.

Expression without 1 also seems to give the same result without selections, but with selections it may differ.

If(Sum(ACT_AMT) > 0 or Sum(FCST_AMT) > 0,

RangeSum(Above(TOTAL If(FISCAL_MONTH_YEAR <= Monthend(Max({<ACT_AMT={">0"}>}TRX_DATE)), Sum(ACT_AMT), Sum(FCST_AMT)), 0, RowNo())))

If you need a hybrid of the two expressions where you want it to be changed based on certain selections and not changed on others, you may be able to modify the expression.

I hope this will help.

Best,

Sunny