Display from most recent month with data

[Anonymous]

I have my source data in an excel spreadsheet with dimensions as months for each row, and columns for data. Each month end, new data is filled out for the month manually.

CalDate | Unfilled Positions | Employee Turnover | Total Employees | Work-Related Injuries | Retirement Notices Given

..

....

2018-08     2     1.40%     74     0     5

2018-09     2     1.40%     76     0     7

2018-10     2     1.30%     80     0     7

2018-11    n/a     n/a          n/a     n/a     n/a      **no data on this line or future dates yet, just placeholder months pre-filled in first column**

...

......

I have text objects setup that I would like to show data from the most recent month's data only. I am using max(aggr) function, but is there a better way? My excel sheet is already pre-filled for future months. Can I use set analysis here to see if there exists data and use the most current month?

Text Object 1 - Works good, shows 80

=MAX(aggr(SUM([Total Employees]),CalDate))

Text Object 2 - Wrong, Shows 6 instead of what I wanted to show 2

=MAX(aggr(SUM([Unfilled Positions]),CalDate))

Text Object 3 - Wrong, Shows 1.40% instead of 1.30%

=num(MAX(aggr(SUM([Employee Turnover]),CalDate)),'##.#%')

Text Object 4 - Works good, shows 7

=MAX(aggr(SUM([Retirement Notices Given]),CalDate))

Text Object 5 - Wrong, shows 2 instead of 0

=MAX(aggr(SUM([Work-Related Injuries]),CalDate))

Can anyone help me improve this? Thank you!

[jerifortune]

You can do the following
1. On Data Load Script add a Column with this expression  - PurgeChar([FY Period],'-') AS YearMonth

2. Create a variable for max Month in each calculation- example, the max month for calculating employee turnover should be - Max({<[HR - Employee Turnover (YTD)]=- {""}>}YearMonth)

3. Use the variable in step 2 for Employee Turnover Value - if(YearMonth=$(vMaxYr1), [HR - Employee Turnover (YTD)])

vMaxYr1 is the name of the variable

[Anonymous]

Jerry, I really appreciate your reply and help.

It just shows "-"  in my text object when I use that expression.

Maybe it is just something I am overlooking - trying to learn a lot in a short amount of time.

[jerifortune]

Use this in your chart (textbox) expression.

only(if(YearMonth='$(vMaxYr1)', [HR - Employee Turnover (YTD)]))

The earlier expression works on table object

[jerifortune]

I have adjusted your app. You should be fine now.

[jerifortune]

Hi Adam,

Did this work for you?