Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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])
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])
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.
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?
It's created as above.
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.
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.