Done , but quite tricky.
I used table calc, with hiding >12 months old data <<Year Diff>>...after filtering 0~2 months old data <<Date Header>>.
[Date_Header] // Used as filter to Show only this month, -1 month, and -2 month of this year, last year
if datediff('month',[Date],today())=0 or
// for the purpose of grouping <0~-11 month>, <-12 month+ older > Keeping Table calc, but want to hide base month of last year
// You can understand why I add this field in the sheet "Why Year Diff Needed?"
Then, put Three MIN([Number of Records]) field into Column to have three different measure independently
SUM([Total Target Emp])/Sum([Total Emp])
2. [EmplRatio] Table calc difference from previous Year Diff which is sorted as descendant
3. KPI up/down with shape and color,
if ZN([EmplRatio]) - LOOKUP(ZN([EmplRatio]), -1)<=0.05 then "Up" else "Down" END
Hide "1" of Year Diff
Why [Year Diff] Needed?
There is another way to use LOD, but that way is also troublesome, maybe this way is a little bit easier....
CalcDiffMonths_rep_ver9_1.twbx 36.3 KB
thank you so much for the explanation and your help. I really appreciate it. I am going over everything, seeing what you did. I think I may have couple further questions, but want to make sure I understand it all before asking. Thank you so much!
It'll take time, I think. As I said it's tricky, but for sure, good learning opportunity.
Don't hesitate ask questions at any time
Thank you so much. Yes - this is really good learning opportunity. Your approach to handle the previous/current year change is clever and it works! Thanks so much. I think I have it working. I added a column called "last year" ratio number to show the % of the ratio that was last year for the indicated month.
I added the table calc field to the column it at the end after I added "Current year", "change", "+/-". Do you know how to move it after "Current year"? When i try to just move or drag, it messes up the numbers for all other columns and it doesn't just move.
Also, what is the purpose for the "Measure Names" in the Filter section? How did you get the "Difference in EmplRatio from the Previous along FiscalYear" in the selection ? (When I right click -> Filter, i don't have that in there)
Is there an easy way to sort the months in order of most recent to furthest, 0 , -1, -2. Example: February (now and most recent), January (last month), December (furthest from now).
It was working file and I think you can safely remove Measure Name filter.
Sorting is another tricky one because "Month Description" is used to judge "Same Month" across year.
I mean adding year brings you incorrect data or null immediately.
Then the approach is same as year difference, calculate "Month Difference".
Then add these number on left side of "Month Description"
These 0,1,2 are unnecessarily information for users but need for correct sorting.
Put below field instead of "Month description"
[Month Description 2]
str(datediff('month',[Date],today())%12)+" "+[Month Description]
==fyi== /// Operators
This calculates a numeric remainder. For example,
5% 4 = 1.
CalcDiffMonths_rep_2_ver9_1.twbx 37.9 KB
Yes, that worked! Again, thank you so much for your help on this - it has been good learning experience.
1 of 1 people found this helpful
Happy to hear so.
One more request, if you can mark "helpful" again, it encourages me
Done! Thank you.