Grouping the pivot based on date range

[Pre2479]

Hi Alteryx team, I am really looking forward for your help with creating a pivot table for account category and actions grouping them based on date range in months (data manipulated). Actions are based on certain status and status code such as. Status='New Account'= Investigate Account Details. Status code='Missing entry' = Add to list. Status code='Resolved' = Investigate Closure Details. Date range to be grouped is based on Next review date in months -0-3, 3-6, 6-9 etc. Also, need to insert a row with Group and owner name to the pivot. Please find the data and the desired output files.

[caltang]

Hi @Pre2479 

Something like this?

[Yoshiro_Fujimori · Accepted solution]

@Pre2479 ,

I excluded "Owner" as I cannot find it in your data.

Input

Workflow

Formula

months =

DateTimeDiff(
DateTimeParse([Next review date],"%d/%m/%Y"),
DateTimeToday(),
"months")

Next review date range =

IF [months]<=3 THEN "0-3"
ELSEIF [months]<=6 THEN "3-6"
ELSEIF [months]<=9 THEN "6-9"
ELSEIF [months]<=12 THEN "9-12"
ELSEIF [months]<=24 THEN "12-24"
ELSEIF [months]<=36 THEN "24-36"
ELSEIF [months]<=48 THEN "36-48"
ELSEIF [months]<=60 THEN "48-60"
ELSE ">60"
ENDIF

Output

If you want to make it to a pretty report, you may want to use Table tool.

Here is a good resource: Tool Mastery | Table 

Good luck!

[Pre2479 · Accepted solution]

Many many thanks Yoshiro, your solution worked like a charm. thankyou so much.