Counting records based on consecutive dates

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,

DataSet.csv

Workflow

Accepted answers

danilang

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

PlanValues v2.yxzp

All comments

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

PlanValues.yxzp

carlosteixeira

Hi @Mostafa_Anwar

Something like that?

Best regards

Counting records based on consecutive dates.yxmd

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,

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703