topic Re: Alteryx Date filter in General Discussions

Alteryx Date filter

rbanola1 — Wed, 05 Jul 2023 20:17:20 GMT

Hi,

I want to create a date filter for my Alteryx Practice.

I have a row named AsOfDate which contains a random date (filled by me of year 2023) in yyyymmdd in string format

my question is

Condition1: if AsOfDate column's field is blank then i want Alteryx to select the date itself and fetch data from current date quarter wise- for example:

if blank the convert year to today's year

convert month to "1" if month is between 1-3 (Q3)

convert month to "4" if month is between 4-6 (Q4)

convert month to "7" if month is between 7-9 (Q1)

convert month to "10" if month is between 10-12 (Q2)

and convert day to always "01" of above converted month.

Condition 2: if AsOfDate date is not blank then:

calculate AsofYear, AsofMonth and AsofDay from AsOfDate column fields and convert/parse it into Date in YYYMMDD format.

Please guide me on this, i know its a long question but I have tried my best to explain it. Even slight help would really help me a lot.

thanks

Re: Alteryx Date filter

rbanola1 — Wed, 05 Jul 2023 20:49:47 GMT

I tried applying below code but it is not converting for example month 5 day 27 year 2023 to month 4 day 1 2023.

IF IsNull([AsOfDateText]) THEN
DateTimeParse(ToString(DateTimeYear(DateTimeNow())) + "-" +
ToString(IF DateTimeMonth(DateTimeNow()) >=1<= 3 THEN "01"
ELSEIF DateTimeMonth(DateTimeNow()) >=4<= 6 THEN "04"
ELSEIF DateTimeMonth(DateTimeNow()) >=7<= 9 THEN "07"
ELSE "10"
ENDIF) + "-01", "%Y-%m-%d")
ELSE
DateTimeParse(ToString([AsOfDateText]), "%Y%m%d")
ENDIF

Re: Alteryx Date filter

Miles_Waller — Wed, 05 Jul 2023 21:36:35 GMT

For clarification, can you please include a few example rows of AsOfDate values (both populated in yyyyMMdd format and at least one null) and what your expected output is?

Re: Alteryx Date filter

rbanola1 — Thu, 06 Jul 2023 14:39:14 GMT

ok sure, I will provide some examples. and please ignore previous question I will share revised question here.

Re: Alteryx Date filter

rbanola1 — Thu, 06 Jul 2023 14:56:38 GMT

Hi,

based on above question some minor changes:

I want to create a date filter for my Alteryx Practice.

I have a row named AsOfDate which contains a random date (filled by me of year 2023) in yyyymmdd in string format (dummy join PNG file)

my question is

Condition1: if AsOfDate column's field is blank (consider 20220527 is blank in dummy join file) then i want Alteryx to calculate year based on today's date, calculate month based on today's date then put day as first day of that month. (Dates png file)

now I want alteryx to calculate data from this AsOfDate to CutOff date (which is 2 years back from AsOfDate - example If AsOfDate calculated by alteryx is 03/31/2023 then cutoff date is 03/31/2021

Condition 2: if AsOfDate date is not blank/is given for example in dummyjoin png file date is given in string format 20220527 then:

calculate AsofYear, AsofMonth and AsofDay from AsOfDate column fields and convert/parse it into Date in YYYMMDD format and gives me data from AsofDate to cutoff date.

example is AsofDate in string is 20220527 (yyyymmdd) then I should be able to get data from 20220527 to 20200527 and date converted in any standard format like yyyy-mm-dd.