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

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

Rawan
7 - Meteor

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/20184455544
5/2/20184445544
5/3/20184444544
5/4/20184444444
5/5/20184444434
5/6/20184444433
5/7/20183444433
5/8/20183344433
5/9/20183334433
5/10/20183333433
5/11/20183333333
5/12/20183333323
5/13/20183333322
5/14/20182333322
5/15/20182233322
5/16/20182223322
5/17/20182222322
5/18/20182222222
5/19/20182222212
5/20/20182222211
5/21/20181222211
5/22/20181122211
5/23/20181112211
5/24/20181111211
5/25/20181111111
5/26/20181111101
5/27/20181111100
5/28/20180111100
5/29/20180011100
5/30/20180001100
5/31/20180000100

 

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

 

thanks in advance

Rawan

15 REPLIES 15
LordNeilLord
15 - Aurora

Hey @Rawan

 

What are the columns of numbers?

Rawan
7 - Meteor

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

 

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

CharlieS
17 - Castor
17 - Castor

@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
15 - Aurora

Hey @Rawan

 

Is this what you're after?

 

CountWeekdays.PNG

LordNeilLord
15 - Aurora

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
7 - Meteor

@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
15 - Aurora

Hey @Rawan

 

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

Rawan
7 - Meteor

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

 

Rawan

Rawan
7 - Meteor

@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:

 

Holiday Flag.png

 

i highly appreciate your help and suggestions..

 

Thanks

Rawan

Labels