Fiscal Year Starts from October to September
Data file provided only has Fiscal Year and Month along with few Categories and Amount.
I need to get Fiscal Year to Month level data
Fiscal Year | Month | Sum Amount | FY YTD(Month - since input file only has month) |
FY19 | Apr | 4031.11 | 4031.11 |
FY19 | May | 1293883.78 | 1297914.9 |
FY19 | Jun | 55649.6 | 1353564.5 |
FY19 | Jul | 73008.9 | 1426573.4 |
FY19 | Aug | 21853.38 | 1448426.8 |
FY19 | Sep | -524.08 | 1447902.7 |
FY20 | Oct | 1905250.62 | 1905250.62 |
FY20 | Nov | 183154.72 | 2088405.34 |
FY20 | Dec | 6270.15 | 2094675.49 |
FY20 | Jan | 12462.86 | 2107138.35 |
FY20 | Feb | 44379.89 | 2151518.24 |
FY20 | Mar | 49065844.52 | 51217362.8 |
FY20 | Apr | 7350942.82 | 58568305.6 |
FY20 | May | 49489202.01 | 108057508 |
FY20 | Jun | 233368.61 | 108290876 |
FY20 | Jul | 183584.37 | 108474461 |
FY20 | Aug | -3502.22 | 108470958 |
FY20 | Sep | 108470958 |
Résolu ! Accéder à la solution.
You can use a Summarize tool to group all of your data on a fiscal year-month basis since you have multiple divisions and categories. To derive the YTD, you can use the Running total tool. Prior to this, I used a reference table, join, and a sort tool to put the months in the correct fiscal order.
Hope this helps!
When I summarize by Year month and amount it works but in my Target Report I also need to be able to filter by Division and Category to be able to see Fiscal YTD by month based on the Division and Category filters and not including then in the Summarize removes the fields from the output file.
So when i add Category and Division in the Summarize, the running total gets messed up. How do i fix that?
When I summarize by Year month and amount it works but in my Target Report I also need to be able to filter by Division and Category to be able to see Fiscal YTD by month based on the Division and Category filters and not including then in the Summarize removes the fields from the output file.
So when i add Category and Division in the Summarize, the running total gets messed up. How do i fix that?
Hi @Poojaraj123 ,
did you add "Category" and "Division" to "Group By" in the Runnng Total tool?
Best,
Roland
Hi @Poojaraj123 ,
the problem is, that Running Total only considers "existing" months, i.e. if there are values for Apr and May, there will be a Running Total for Apr and May only, but not for Jun, Jul and so on. I've added additional rows for the "missing" months to fill the gaps.
Let me know if it works for you.
Best,
Roland
Thankyou Roland.