Counting records based on consecutive dates

[Mostafa_Anwar]

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,

[danilang]

Hi @Mostafa_Anwar 

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

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]

Hi @Mostafa_Anwar 

Something like that?

Best regards

[Mostafa_Anwar]

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]

Hi @carlosteixeira 

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

Regards,

[danilang · Accepted solution]

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]

Awesome! Many thanks Dan