
1. Re: Calculate the difference of a ratio number between months that are in consecutive years
Shinichiro Murakami Feb 19, 2016 7:46 PM (in response to angelina.fleckenstein)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
datediff('month',[Date],today())=1 or
datediff('month',[Date],today())=2 or
datediff('month',[Date],today())=12 or
datediff('month',[Date],today())=13 or
datediff('month',[Date],today())=14
then [Date]
end
[Year_Diff]
int(datediff('month',[Date],today())/12)
// 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
1. [EmplRatio]
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,
[KPI]
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....
Thanks,
Shin

CalcDiffMonths_rep_ver9_1.twbx 36.3 KB


2. Re: Calculate the difference of a ratio number between months that are in consecutive years
angelina.fleckenstein Feb 21, 2016 2:51 AM (in response to Shinichiro Murakami)Hi Shin,
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!

3. Re: Calculate the difference of a ratio number between months that are in consecutive years
Shinichiro Murakami Feb 21, 2016 7:17 AM (in response to angelina.fleckenstein)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
Shin

4. Re: Calculate the difference of a ratio number between months that are in consecutive years
angelina.fleckenstein Feb 21, 2016 10:22 AM (in response to Shinichiro Murakami)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).
Thank you.

5. Re: Calculate the difference of a ratio number between months that are in consecutive years
Shinichiro Murakami Feb 21, 2016 12:08 PM (in response to angelina.fleckenstein)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
% (modulo)
This calculates a numeric remainder. For example,
5% 4 = 1
.Thanks,
Shin

CalcDiffMonths_rep_2_ver9_1.twbx 37.9 KB


6. Re: Calculate the difference of a ratio number between months that are in consecutive years
angelina.fleckenstein Feb 22, 2016 5:53 PM (in response to Shinichiro Murakami)Yes, that worked! Again, thank you so much for your help on this  it has been good learning experience.
Best Regards,
Angelina

7. Re: Calculate the difference of a ratio number between months that are in consecutive years
Shinichiro Murakami Feb 22, 2016 6:37 PM (in response to angelina.fleckenstein)1 of 1 people found this helpfulHappy to hear so.
One more request, if you can mark "helpful" again, it encourages me
Thanks,
Shin

8. Re: Calculate the difference of a ratio number between months that are in consecutive years
angelina.fleckenstein Feb 22, 2016 9:00 PM (in response to Shinichiro Murakami)Done! Thank you.