Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Need help with calculating Fiscal Year to Date(month)

Poojaraj123
6 - Meteoroid

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 YearMonthSum AmountFY YTD(Month - since input file only has month)
FY19Apr4031.114031.11
FY19May1293883.781297914.9
FY19Jun55649.61353564.5
FY19Jul73008.91426573.4
FY19Aug21853.381448426.8
FY19Sep-524.081447902.7
FY20Oct1905250.621905250.62
FY20Nov183154.722088405.34
FY20Dec6270.152094675.49
FY20Jan12462.862107138.35
FY20Feb44379.892151518.24
FY20Mar49065844.5251217362.8
FY20Apr7350942.8258568305.6
FY20May49489202.01108057508
FY20Jun233368.61108290876
FY20Jul183584.37108474461
FY20Aug-3502.22108470958
FY20Sep 108470958
7 REPLIES 7
echuong1
Alteryx Alumni (Retired)

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.

 

echuong1_0-1597103738424.png

 

Hope this helps!

 

Poojaraj123
6 - Meteoroid

 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?

Poojaraj123
6 - Meteoroid

 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?

RolandSchubert
16 - Nebula
16 - Nebula

Hi @Poojaraj123 ,

 

did you add "Category" and "Division" to "Group By" in the Runnng Total tool?

 

Best,

 

Roland

Poojaraj123
6 - Meteoroid

Hello Roland,

 

I tried that option too. I added Division and Category to Summarize (as Group by) and Division and Category to Running Total as well (attached are the images) and it still doesn't give me the right results.

RolandSchubert
16 - Nebula
16 - Nebula

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

Poojaraj123
6 - Meteoroid

Thankyou Roland.

Labels