Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All, I have a complicated data set and I am having a hard time creating a straight table to show YTD vs Prior Year for the same months*. If anybody can assist please provide your input. If you can provide a workbook that would be much appreciate it.
This is the current stay of the data:
Date | Health | 401K | Training |
1/1/2014 | 15 | 10 | 3 |
1/2/2015 | 123 | 15 | 4 |
2/6/2015 | 15 | 13 | 5 |
3/1/2015 | 20 | 51 | 5 |
4/1/2015 | 63 | 32 | 32 |
5/1/2015 | 15 | 26 | 12 |
6/1/2015 | 26 | 46 | 15 |
1/6/2016 | 32 | 32 | 31 |
2/9/2016 | 15 | 14 | 12 |
This is the straight table I want to create:
Fund Name | YTD as Feb 2016 | YTD as Feb 2015 | Variance |
Health | 47 | 138 | -91 |
401K | 46 | 64 | -18 |
Training | 43 | 9 | 34 |
*Note that there might be different dates in the same month. I want to compare the months irrespective of the dates.
Hi ,
Can you say which aggregation function you used to calculate in your straight table output.
You want something like this?
I used a crosstable to transpose your data, not sure if you already have it like this or if you have different columns for each FundName.
See if the attached is helpful.
Script:
Table:
CrossTable(FundName, Value, 3)
LOAD Date,
MonthName(Date) as MonthYear,
Month(Date) as Month,
Health,
[401K],
Training
INLINE [
Date, Health, 401K, Training
1/1/2014, 15, 10, 3
1/2/2015, 123, 15, 4
2/6/2015, 15, 13, 5
3/1/2015, 20, 51, 5
4/1/2015, 63, 32, 32
5/1/2015, 15, 26, 12
6/1/2015, 26, 46, 15
1/6/2016, 32, 32, 31
2/9/2016, 15, 14, 12
];
Dimension:
FundName
Expressions
1) =Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date))) & '<=' & Date(MonthEnd(Max(Date))))"}, MonthYear, Month>}Value)
2) =Sum({<Date = {"$(='>=' & Date(YearStart(Max(Date), -1)) & '<=' & Date(MonthEnd(Max(Date), -12)))"}, MonthYear, Month>}Value)
3) =Column(1) - Column(2)
Best,
Sunny
hi,
Find the attached.
$@M.
Hi ,
Please find this Attachment . Maybe it match your requirement .
Thanks. You guys rock!
If your question is now answered, please flag the Correct Answer and possible Helpful answers.
If not, please make clear with which part of this topic you still like to get help .