Set analysis expression for current/previous month not working

[Anonymous]

I have this expression, but it doesn't show the max YearMonth Sales if nothing is selected, it shows all sales for the year

sum({$<YearMonth={$(=Max(YearMonth))}, 

Year, 

Quarter, 

YearMonth>}Sales)

Likewise the expression for the Previous YearMonth doesn't work either. It shows the same data as the Max YearMonth

sum({$<YearMonth={$(=Max(YearMonth)-1)}, 

Year, 

Quarter, 

YearMonth>}Sales)

Please Help!

Thank you!!

[Anonymous]

=Date(addmonths(Max(%_DateKey),-1),'MMM-YYYY')

returned Aug-2011

[kaushiknsolanki]

Hi,

   It means your %_DateKey is having max date for  Oct-2011

   am I right..?

Regards,

Kaushik Solanki

[Anonymous]

Sorry, I'm not sure if I know how to do what you're asking.

I have opened to a sheet with a chart that shows sales for each month.

Does that work okay for you if I upload it like that?

[kaushiknsolanki]

Ok No Problem Also if you can tell me in what format you want the data.

[Anonymous]

%_DateKey has the max date Sep-2011

I uploaded a couple files since I wasn't sure.

I'm not sure what format I want it in. 

Can you explain??

all I know is I need the Set Analysis expression for current and previous months sales.

are there different formats to do that?

[kaushiknsolanki]

HI,

    Create a chart with dimension as Pharmacy Group and expression1 as

    Sum({<MonthYear = {"$(=MaxString(%_DateKey))"}>}Sales)

    Expression2 as

    sum({<MonthYear = {"$(=Date(Addmonths(Max(%_DateKey),-1),'MMM-YYYY'))"}>}Sales)

    Expression1 for current Month and Expression 2 for Previous Month

    For more have a look at the application attched.

Regards,

Kaushik Solanki

[Anonymous]

Expression 1 isn't working for me. It returns a value of 0

Expression 2 works if nothing is selected, but once I select a month, it returns a value of 0

[Anonymous]

I can see it works in the model you uploaded.

Do the expression you used only work in charts?

I need it in charts, but also other places, like text boxes that allow a user to compare current and previous months sales

[kaushiknsolanki]

Hi,

    It is working in Chart at my side.

    And it should also work in the textbox.

    Can you please send me the screen shot of that sheet with the selection where you are getting zero.

Regards,

Kaushik Solanki

[Anonymous]

I took snapshots and attached them.