Return text value from last month in a straight table

[zakpullen]

Hi,

In the example below, I would like to be able to see in a straight table where a cost centre (against assignment number) has changed from the previous month. As this is not an aggregation, I'm not sure how to do this.

Many thanks.

[sunny_talwar · Accepted solution]

Not sure how MonthYear is created in the script... but assuming a script similar to this

Table:
LOAD Date(MonthStart(Date#(MonthYear, 'MMM-YYYY')), 'MMM-YYYY') as MonthYear,
	 [Assignment Number],
	 [Cost Centre];
LOAD * INLINE [
    MonthYear, Assignment Number, Cost Centre
    Jan-2019, 1234, AAA
    Jan-2019, 3214-2, BBB
    Jan-2019, 5453, CCC
    Jan-2019, 7444, DDD
    Feb-2019, 1234, AAA
    Feb-2019, 3214-2, BBB
    Feb-2019, 5453, CCC
    Feb-2019, 7444, CCC
];

Try an expression like this for previous month

=Only({<MonthYear = {"$(=Date(MonthStart(Max(MonthYear), -1), 'MMM-YYYY'))"}>} [Cost Centre])

[zakpullen]

Thanks Sunny.

MonthYear is generated as below in the calendar. PeriodDate is the effective date for each monthly report in the relevant file.

Date(MonthStart(PeriodDate),'MMM-YYYY') as MonthYear,

Your solution worked in a simplified version of my app (where MonthYear is created in the data table script, no  calendar), but doesn't work once the calendar is reintroduced (and MonthYear is created there).

I can't upload the app to provide more context unfortunately. Ultimately, I want a table that only shows rows where the cost centre has changed since last month. I'll keep playing with it.

[sunny_talwar]

How is the monthyear field created in the calendar script? is it created in the same way as the above or do you use another script for monthyear field?

[zakpullen]

It's created as above.

[sunny_talwar]

Can you explain the behavior that you see? Does it happen based on selection in specific fields or does it happen with or without selections? The more info you can provide, the easier it will be to help.

[zakpullen]

Financial year fields from the calendar were the problem. IT now seems to be working ok with the small amendments below.

Only({<FMonth=,FY=,MonthYear = {"$(=Date(MonthStart(Max(MonthYear),-1), 'MMM-YYYY'))"}>} [Cost Centre])

Many thanks for you help.