topic Re: Using MonthEnd in If Statement in QlikView

Using MonthEnd in If Statement

cbaqir — Fri, 24 Jul 2015 19:32:36 GMT

I have a graph (attached) that shows Actuals in a solid line and then turns into a dotted line for Forecasted hours.

The actuals get posted on the last day of the month so since it is currently July, I would expect to see the solid line end in June. Instead, it's showing through the end of this month. I tried to overcome this by using:

Dimension: =date(FISCAL_MONTH_YEAR, 'YYYY-MMM')

Expression: if(FISCAL_MONTH_YEAR>Monthend(today())+1,sum(FCST_AMT),sum(ACT_AMT))

Line Style: if([FISCAL_MONTH_YEAR]>today(),'<S3>','<S1>')

Even though the dotted line is starting in August now, my July date has disappeared. which shouldn't be the case.

How do I get the current month to display but the dotted line starting from the last month?

Thanks,

Cassandra

Re: Using MonthEnd in If Statement

sunny_talwar — Fri, 24 Jul 2015 20:22:08 GMT

This?

Re: Using MonthEnd in If Statement

cbaqir — Fri, 24 Jul 2015 21:22:03 GMT

Wow, that is complicated!

I've never used RangeSum(Above()) before. Thanks!

Re: Using MonthEnd in If Statement

cbaqir — Fri, 24 Jul 2015 21:32:24 GMT

But when I applied this to my chart I still don't see July. Any idea why? I copied over the formulas verbatim.

Re: Using MonthEnd in If Statement

sunny_talwar — Fri, 24 Jul 2015 21:34:57 GMT

‌I did change the expression around also. Try imitating the expression change as well.

Re: Using MonthEnd in If Statement

cbaqir — Fri, 24 Jul 2015 21:39:08 GMT

I copied the expression and line style over completely. Have you ever seen one month of the dimension just disappear before?

Re: Using MonthEnd in If Statement

sunny_talwar — Fri, 24 Jul 2015 21:44:07 GMT

‌HAhaha I have not... But it seemed like the Full Acumulation Dynamics with the if statement issue to me. have you moved the full accumulation back to no accumulation?

Re: Using MonthEnd in If Statement

cbaqir — Mon, 27 Jul 2015 14:44:08 GMT

Good idea but unfortunately, it didn't fix it. If I change <= to only = in the expression, it shows up again but then I lose my historical data. I'm not familiar with all of the functions in this If statement but I will continue to play with it to see if I can find a combination that works.

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

Re: Using MonthEnd in If Statement

sunny_talwar — Mon, 27 Jul 2015 14:49:01 GMT

I really can't say much without seeing what is going on because in the qvw I attached, it seems to be showing all months, right? I hope I am not missing a month by chance

Re: Using MonthEnd in If Statement

cbaqir — Mon, 27 Jul 2015 15:32:19 GMT

Here's an updated sample with your expressions applied to my chart.

Re: Using MonthEnd in If Statement

sunny_talwar — Mon, 27 Jul 2015 15:47:15 GMT

Check it out:

New 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())))

Re: Using MonthEnd in If Statement

cbaqir — Mon, 27 Jul 2015 17:21:13 GMT

You are amazing! Is it necessary/correct to have No Accumulation?

The intent of this chart is to show Actuals historically and Forecast for the future.

Re: Using MonthEnd in If Statement

sunny_talwar — Mon, 27 Jul 2015 17:26:10 GMT

Not necessary, but you will see double accumulation if you have it selected because RangeSum is already doing the accumulation for you and you will Accumulate the accumulated value

I hope that make sense.

Re: Using MonthEnd in If Statement

cbaqir — Mon, 27 Jul 2015 17:28:00 GMT

Thanks. Are there any tutorials about RangeSum and Above that I could look into?

Re: Using MonthEnd in If Statement

sunny_talwar — Mon, 27 Jul 2015 17:30:21 GMT

See if these helps: