Modify an expression for different function based on month

[elliot9821]

Hi All,
I am currently working on a line chart (in QlikCloud) to display differences between budgeted revenue, last years revenue, and this years revenue (using cumulative sum). I am also trying to have this months revenue line stop at the current day (rather than displaying flat lining).

The below expression results in the following graph when selecting the current month. This is the desired outcome for viewing current month (cut the line for this year off at today).

if(day <= day(today()), RangeSum(Above(TOTAL Sum(this_year_daily_total), 0, RowNo(TOTAL))))

Cutoff at current day of current month

Using the below code results in the ideal graph for all other months (includes all days of month for daily sales)

RangeSum(Above(TOTAL Sum(this_year_daily_total), 0, RowNo(TOTAL)))

Full Month of sales example

I have tried messing around with expressions but have had no luck making an expression that does the following:

1) Cut off daily sales at current day if viewing the chart and filtering by current month
2) If condition 1) not met, default back to viewing the full cumulative sum of daily sales for all OTHER months. 

Any help with the expressions needed to do this would be greatly appreciated. 

[Rohan]

Hi Elliot,

Have you tried :

RangeSum(Above(TOTAL Sum({<Date={">=$(=monthstart(max(Date)))<=$(=date(max(Date)))"}>}this_year_daily_total), 0, RowNo(TOTAL))))

Regards,

Rohan.

[Gabbar]

The problem here is a measure becomes 0 when there is no corresponding data with respect to the dimension. You need to make it null.
and please make the format for day as num so it would be easy.

try using if statement in your measure like:-
If (Num(Date)<=Num(today()),Cumulative_Expression_here,null())

[elliot9821]

Hi Rohan,
This does work in terms of displaying both current month and other month data, however it does still display the flat line for the current month (after current date). 
Do you have any suggestions for having this data stop displaying after current day of current month?

Current Month displays data after today

[Rohan]

Hi Elliot,

Try this :

if(RangeSum(Above(TOTAL Sum({<Date={">=$(=monthstart(max(Date)))<=$(=date(max(Date)))"}>}this_year_daily_total), 0, RowNo(TOTAL))))=above(total RangeSum(Above(TOTAL Sum({<Date={">=$(=monthstart(max(Date)))<=$(=date(max(Date)))"}>}this_year_daily_total), 0, RowNo(TOTAL))))),

null(),

RangeSum(Above(TOTAL Sum({<Date={">=$(=monthstart(max(Date)))<=$(=date(max(Date)))"}>}this_year_daily_total), 0, RowNo(TOTAL)))))

or

if(day <= day(max(Date)), RangeSum(Above(TOTAL Sum(this_year_daily_total), 0, RowNo(TOTAL)))) 

Regards,

Rohan.