Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
cbaqir
Specialist II
Specialist II

Using MonthEnd in If Statement

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

1 Solution

Accepted Solutions
sunny_talwar

Check it out:

Capture.PNG

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



View solution in original post

14 Replies
sunny_talwar

This?

Capture.PNG

cbaqir
Specialist II
Specialist II
Author

Wow, that is complicated!

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

cbaqir
Specialist II
Specialist II
Author

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

sunny_talwar

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

cbaqir
Specialist II
Specialist II
Author

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

sunny_talwar

‌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?

cbaqir
Specialist II
Specialist II
Author

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

sunny_talwar

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

cbaqir
Specialist II
Specialist II
Author

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