Expression to view previous month data not working

[#1]

Hi guys,

In my load statement I have

    "Date",

    Month(Date) as "Month",

    Year(Date) as "Year",

    Date(MonthStart(Date), 'MMM-YY') as MonthYear,

    Price

Which works fine. And when I want to view the max MonthYear selected with the expression:

Sum({<MonthYear = {"$(=max(MonthYear))"}>}Price)

That works fine for whichever MonthYear I select. However when I try the expression:

Sum({<MonthYear = {"$(=max(MonthYear)-1)"}>}Price)

It never seems to work as it only returns 0. My max MonthYear is Jan-15, so one before would be Dec-14. Can anyone see what I'm doing wrong? I'm not too good when it comes to this stuff so please let me know if something else doesn't look right.

Thanks in advance.

Also I'm not able to post the file, sorry!

[ToniKautto]

The expression inside the dollar expansion is evaluated before the set expression is processed. The result will then behave as a constant.

For example put the expression =max(MonthYear)-1 in a text object and you will find out which value it will expand in to in the chart expression. If this result for example is 2014, the expression in the chart will be Sum({<MonthYear = {"2014"}>}Price) for all rows. The dollar expanded value is not re-evaluated for each row in the chart.

To make the expression adapt to each row you need to use an IF statement, for example something like  Sum(If(MonthYear=max(MonthYear)-1,Price)). Then you might need to make the Max() aggregation span over the total data if it should be based on all the current data and not for the specif dimension value.

=Sum(If(MonthYear=max(TOTAL MonthYear)-1,Price))

Make the Max() aggregation a separate expression to see what it returns per row, and you should be able to predict if it is correct for you intentions or not.

=max(TOTAL MonthYear)-1

[#3]

Hi Toni,

You've really helped my understanding of all this, thanks! However the value returned is still 0 when using =Sum(If(MonthYear=max(TOTAL MonthYear)-1,Price)).

When I put =max(MonthYear) in the text box it would give me Jan-15, but when I put in =max(MonthYear)-1 it would return the number 42004.

I'm a bit stuck now.

Edit:

Oh I see, =max(MonthYear)-1 gives me 31/12/2014 when I want it to give me 01/12/2014.

Can this be resolved?

I should clarify. I only have data on the first of each month, not any other date. Even then I should still be receiving the sum of the entire date, right?

[ToniKautto]

Correct! My bad, I should have seen that earlier.

Since one day back will give you the last day in previous month, MonthStart on that value should give the first date of the previous month.

=MonthStart(max(MonthYear)-1)

I am still think the IF statement is the way to do it

[ToniKautto]

Would this work?

Sum( If( MonthYear=MonthStart(max(TOTAL MonthYear)-1 ), Price) )

[#6]

I was just trying to work this equation out with no luck haha. That works perfectly thank you so much!

I'll definitely follow this kind of structure in the future.

[#7]

Another quick question, if I were to use set analysis in this equation (eg. Index = Y), how would I incorporate that?

[ToniKautto]

The main difference in this case is that the set expression will be applied in the same way as a selection, and then the aggregation is done on that data set. The limited data set simply will not include the rows you are looking for.

The IF statement on the other hand will be evaluated on each row of the current data set, and by that it will be able to target the rows you are looking for.

Good to hear it worked!

[ToniKautto]

My general advise is that if an IF statement can be replaced with a set expression it would typically give better performance due to the aggregation being processed in a smaller data set. However it will not always be an option, as the aggregation purpose and the data set required for that will dictate your technical option.

Possibly you can sometimes pull together a complex set expression to give you the required data set, but then you need to keep in mind that the data set composition will require processing and occupy memory. An IF statement can theoretically be quicker in such a case and also occupy less memory.

Rule thumb is to make the simplest implementation. If you are happy with the performance and the results asre correct, it is good enough.

[#10]

Hi Toni,

Unfortunately there's still one more thing I want it to do which it isn't doing.

I have a filterpane next to it which has my MonthYear dimension. When I select a different month, such as Dec-14, I want it to show data for Nov-14. Instead it just shows 0. 😕