Count Sundays, Mondays, Tuesdays,... in a month

[Rawan]

Hello everyone,

I am wondering if there is a way to count how many weekdays in a month, but each weekday separate on a daily basis. here is an example of what i did in excel to achieve this:

5/1/2018	4	4	5	5	5	4	4
5/2/2018	4	4	4	5	5	4	4
5/3/2018	4	4	4	4	5	4	4
5/4/2018	4	4	4	4	4	4	4
5/5/2018	4	4	4	4	4	3	4
5/6/2018	4	4	4	4	4	3	3
5/7/2018	3	4	4	4	4	3	3
5/8/2018	3	3	4	4	4	3	3
5/9/2018	3	3	3	4	4	3	3
5/10/2018	3	3	3	3	4	3	3
5/11/2018	3	3	3	3	3	3	3
5/12/2018	3	3	3	3	3	2	3
5/13/2018	3	3	3	3	3	2	2
5/14/2018	2	3	3	3	3	2	2
5/15/2018	2	2	3	3	3	2	2
5/16/2018	2	2	2	3	3	2	2
5/17/2018	2	2	2	2	3	2	2
5/18/2018	2	2	2	2	2	2	2
5/19/2018	2	2	2	2	2	1	2
5/20/2018	2	2	2	2	2	1	1
5/21/2018	1	2	2	2	2	1	1
5/22/2018	1	1	2	2	2	1	1
5/23/2018	1	1	1	2	2	1	1
5/24/2018	1	1	1	1	2	1	1
5/25/2018	1	1	1	1	1	1	1
5/26/2018	1	1	1	1	1	0	1
5/27/2018	1	1	1	1	1	0	0
5/28/2018	0	1	1	1	1	0	0
5/29/2018	0	0	1	1	1	0	0
5/30/2018	0	0	0	1	1	0	0
5/31/2018	0	0	0	0	1	0	0

I am not sure if it is doable in Alteryx, but I am hoping it is..

thanks in advance

Rawan

[LordNeilLord]

Hey @Rawan

What are the columns of numbers?

[Rawan]

Aww, I am sorry I didn't notice the headers were gone. they are:

Sunday, Monday, Tuesday, Wednesday, Thursday, Friday, Saturday

[CharlieS]

@Rawan

This would be a perfect time to use the %a or %A datetime format specifiers. Generate a row for each day, use these formatting specifiers and Summarize it up.

%a = The abbreviated weekday name (Mon, Fri)

%A = The long weekday name (Monday, Friday)

[LordNeilLord]

Hey @Rawan

Is this what you're after?

[LordNeilLord · Accepted solution]

Looks like there is an issue with uploading attachments today

I've put the xml into this post...if you copy it into notepad and save as a yxmd

Spoiler

<?xml version="1.0"?>
<AlteryxDocument yxmdVer="11.7">
  <Nodes>
    <Node ToolID="2">
      <GuiSettings Plugin="AlteryxBasePluginsGui.GenerateRows.GenerateRows">
        <Position x="114" y="78" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <UpdateField value="False" />
          <UpdateField_Name />
          <CreateField_Name>Date</CreateField_Name>
          <CreateField_Type>Date</CreateField_Type>
          <CreateField_Size>10</CreateField_Size>
          <Expression_Init>[StartDate]</Expression_Init>
          <Expression_Cond>Date &lt; DateTimeAdd(DateTimeAdd(StartDate, 1, "months"),-1, "days")</Expression_Cond>
          <Expression_Loop>DateTimeAdd(Date, 1, "days")</Expression_Loop>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxGenerateRows" />
    </Node>
    <Node ToolID="3">
      <GuiSettings Plugin="AlteryxBasePluginsGui.TextInput.TextInput">
        <Position x="18" y="78" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <NumRows value="1" />
          <Fields>
            <Field name="StartDate" />
          </Fields>
          <Data>
            <r>
              <c>2018-05-01</c>
            </r>
          </Data>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxTextInput" />
    </Node>
    <Node ToolID="4">
      <GuiSettings Plugin="AlteryxBasePluginsGui.AlteryxSelect.AlteryxSelect">
        <Position x="186" y="78" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <OrderChanged value="False" />
          <CommaDecimal value="False" />
          <SelectFields>
            <SelectField field="*Unknown" selected="True" />
          </SelectFields>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxSelect" />
    </Node>
    <Node ToolID="5">
      <GuiSettings Plugin="AlteryxBasePluginsGui.GenerateRows.GenerateRows">
        <Position x="270" y="78" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <UpdateField value="False" />
          <UpdateField_Name />
          <CreateField_Name>Date2</CreateField_Name>
          <CreateField_Type>Date</CreateField_Type>
          <CreateField_Size>10</CreateField_Size>
          <Expression_Init>[Date]</Expression_Init>
          <Expression_Cond>Date2 &lt; DateTimeAdd(DateTimeAdd(StartDate, 1, "months"),-1, "days")</Expression_Cond>
          <Expression_Loop>DateTimeAdd(Date2, 1, "days")</Expression_Loop>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxGenerateRows" />
    </Node>
    <Node ToolID="6">
      <GuiSettings Plugin="AlteryxBasePluginsGui.Formula.Formula">
        <Position x="342" y="78" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <FormulaFields>
            <FormulaField expression="DateTimeFormat([Date2],&quot;%A&quot;)" field="DoW" size="1073741823" type="V_WString" />
            <FormulaField expression="ToNumber(DateTimeFormat([Date2],&quot;%d&quot;))" field="DOW Num" size="2" type="Int16" />
          </FormulaFields>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText>DoW = DateTimeFormat([Date2],"%A")
DOW Num = ToNumber(DateTimeFormat([Date2],"%d...</DefaultAnnotationText>
          <Left value="False" />
        </Annotation>
      </Properties>
      <EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxFormula" />
    </Node>
    <Node ToolID="7">
      <GuiSettings Plugin="AlteryxBasePluginsGui.CrossTab.CrossTab">
        <Position x="450" y="78" />
      </GuiSettings>
      <Properties>
        <Configuration>
          <GroupFields>
            <Field field="Date" />
          </GroupFields>
          <HeaderField field="DoW" />
          <DataField field="DOW Num" />
          <Methods>
            <Method method="Count" />
          </Methods>
        </Configuration>
        <Annotation DisplayMode="0">
          <Name />
          <DefaultAnnotationText />
          <Left value="False" />
        </Annotation>
        <MetaInfo connection="Output">
          <RecordInfo>
            <Field name="Date" source="CrossTab:Group:Count:" type="Date" />
            <Field name="Count_Friday" source="CrossTab:Header:DoW:Friday:CountNonNulls:" type="Double" />
            <Field name="Count_Monday" source="CrossTab:Header:DoW:Monday:CountNonNulls:" type="Double" />
            <Field name="Count_Saturday" source="CrossTab:Header:DoW:Saturday:CountNonNulls:" type="Double" />
            <Field name="Count_Sunday" source="CrossTab:Header:DoW:Sunday:CountNonNulls:" type="Double" />
            <Field name="Count_Thursday" source="CrossTab:Header:DoW:Thursday:CountNonNulls:" type="Double" />
            <Field name="Count_Tuesday" source="CrossTab:Header:DoW:Tuesday:CountNonNulls:" type="Double" />
            <Field name="Count_Wednesday" source="CrossTab:Header:DoW:Wednesday:CountNonNulls:" type="Double" />
          </RecordInfo>
          <SortInfo>
            <Field field="Date" order="Asc" />
          </SortInfo>
        </MetaInfo>
      </Properties>
      <EngineSettings EngineDll="AlteryxBasePluginsEngine.dll" EngineDllEntryPoint="AlteryxCrossTab" />
    </Node>
  </Nodes>
  <Connections>
    <Connection>
      <Origin ToolID="2" Connection="Output" />
      <Destination ToolID="4" Connection="Input" />
    </Connection>
    <Connection>
      <Origin ToolID="3" Connection="Output" />
      <Destination ToolID="2" Connection="Input" />
    </Connection>
    <Connection>
      <Origin ToolID="4" Connection="Output" />
      <Destination ToolID="5" Connection="Input" />
    </Connection>
    <Connection>
      <Origin ToolID="5" Connection="Output" />
      <Destination ToolID="6" Connection="Input" />
    </Connection>
    <Connection>
      <Origin ToolID="6" Connection="Output" />
      <Destination ToolID="7" Connection="Input" />
    </Connection>
  </Connections>
  <Properties>
    <Memory default="True" />
    <GlobalRecordLimit value="0" />
    <TempFiles default="True" />
    <Annotation on="True" includeToolName="False" />
    <ConvErrorLimit value="10" />
    <ConvErrorLimit_Stop value="False" />
    <CancelOnError value="False" />
    <DisableBrowse value="False" />
    <EnablePerformanceProfiling value="False" />
    <DisableAllOutput value="False" />
    <ShowAllMacroMessages value="False" />
    <ShowConnectionStatusIsOn value="True" />
    <ShowConnectionStatusOnlyWhenRunning value="False" />
    <ZoomLevel value="0" />
    <LayoutType>Horizontal</LayoutType>
    <MetaInfo>
      <NameIsFileName value="True" />
      <Name>CountWeekdays</Name>
      <Description />
      <RootToolName />
      <ToolVersion />
      <ToolInDb value="False" />
      <CategoryName />
      <SearchTags />
      <Author />
      <Company />
      <Copyright />
      <DescriptionLink actual="" displayed="" />
      <Example>
        <Description />
        <File />
      </Example>
    </MetaInfo>
    <Events>
      <Enabled value="True" />
    </Events>
  </Properties>
</AlteryxDocument>

[Rawan]

@LordNeilLord 

Thank you so much for the quick and amazing workflow, that is exactly what i want to do, i will study your workflow to understand what you did so i can implement it at my end.. I am wondering though if it would still work if i want to include more months, and reset the count of weekdays every new month.. 

Thanks again

Rawan

[LordNeilLord]

Hey @Rawan

Yes it will work for multiple months :) You may need to tweak it 

[Rawan]

DID IT! this is exciting, i will be replacing the Excel sheet that i have been using with this :).. thanks again @LordNeilLord

Rawan

[Rawan]

@LordNeilLord

I am trying to flag Holidays in this workflow, I was able to create a separate piece of tools to find all holidays and flag them with 1, the problem though is i can't take this back to my pivot table, do you have any idea what am i doing wrong? this is how i have it now, but i tried to join the pivot tool after i finish appending the flag to the date data set, but it didn't work too.. this is what i am trying to do:

i highly appreciate your help and suggestions..

Thanks

Rawan