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

Counting records based on consecutive dates

Mostafa_Anwar
8 - Asteroid

Hi All,

 

The attached dataset i am receiving it on a monthly basis and this data representative Oct month,

Basically i want to achieve the following,

1) Change the row number 4 to be as Dates (Day-Month-Year)

2) Add another column after (Grand Total) column called Plan to calculate as per the example below:

    In case of there is a number 1 showing in a consecutive dates as example there is a value (number 1) in dates (15,16,17 Oct 2019) then should be the value to be as 2,

Another example

     In case of there is a number 1 showing in a consecutive dates as example there is a value (number 1) in dates (27,28,29,30 Oct 2019) then should be the value to be as 3

 

So as per the attached dataset example, The user3 has number 1 showing in a consecutive dates (15,16,17 Oct 2019) also has number 1 showing in a consecutive dates (23,24 Oct 2019) , has number 1 showing in a consecutive dates (27,28,29,30,31 Oct 2019) , has number 1 showing in a consecutive dates (2nd,3rd Oct 2019), has number 1 showing in a consecutive dates (7th,8th,9th Oct 2019) so the total should be as 10 represent in Plan column

However for the user6 has number 1 showing in date (27th Oct 2019) so the total should be as 1 represent in Plan column

 

 

Hope i clarified my issue clearly, Your support will be highly appreciated.

 

Regards,

6 REPLIES 6
danilang
19 - Altair
19 - Altair

Hi @Mostafa_Anwar 

 

Here's a workflow that orders your date columns and provides a Plan value

 

w.png

Transpose the Date columns.  Sort rows by the ISO(Alteryx format) date .  The Multirow formula calculates the actual Plan values.  Cross tab/Dynamic rename to get back the dates as columns in the proper format and join this with the summarized Plan for each row.  The final Formula tool adjusts for the entries with only 1 Value in the month (Row 6)

 

Dan 

 

 

 

carlosteixeira
15 - Aurora
15 - Aurora

Hi @Mostafa_Anwar 

 

Something like that?

 

Best regards

 

 

Carlos A Teixeira
Mostafa_Anwar
8 - Asteroid

Many thanks @danilang for the provided workflow, I have noticed that in (Row number 2) there is a consecutive dates (23,24th Oct 2019) also there is a number 1 in date (31st Oct 2019) so the value in Plan column should be 2 instead of 1 as its accumulate non consecutive dates as well.

Also in (Row number 7) there is a number 1 in date (1st Oct 2019) and there is a number 1 in date (10th Oct 2019) so the value in Plan should be 2 instead of 0 as its accumulate the non consecutive dates.

 

So appreciate if you could adjust this in workflow.

 

Many thanks again for your support.

 

Regards,

 

 

Mostafa_Anwar
8 - Asteroid

Hi @carlosteixeira 

 

Unfortunately the provided workflow displayed the values based on the total values in each row.

 

Regards,

 

danilang
19 - Altair
19 - Altair

Hello @Mostafa_Anwar 

 

Here's an amended version that includes single non-consecutive dates in the Plan amount.  Note that the dates that you mention in your previous post, 1st Oct 2019 and 10th Oct 2019, don't correspond to record 7, but they do correspond to record 9.  So the Plan for total 9 is now 2

 

Dan

Mostafa_Anwar
8 - Asteroid

Awesome! Many thanks Dan

Labels