0

Incoming and Outgoing orders by week

I have three columns, first is order number, then entry date, then invoice date.  I would like to be able to plot the total number (count) of incoming orders (entry date) against the total number (count) of outgoing orders (those with an invoice date) by week.  I don't think the order number is important in this case I mention only for reference.  I feel like I keep getting close, but I'm not quite getting there.  It seems like it should be fairly simple.  Can anyone provide a little guidance? Thanks!

 

4 comments

  • 0
    Avatar
    Pratyusa Ray

    Hi, 

    Thanks for posting in our community!

    I created a sample data in excel understanding your use case and imported the data using the excel connector to Klipfolio. The data looks like:

     

    I created a klip as shown below:

    Now for the first column week I selected the Entry date in the formula and under Properties section for the Display format I selected Custom and typed in ww to get the week number. I also added week in the Prefix option under properties to show data in the format "week" followed by the week number.

    For the second column Incoming orders in table I used the LOOKUP function as follows. You can find more information about the Lookup function.

    This formula looks at each week column, then looks for a match in a grouped list of the weeks (formatted to match the display format set above), and then returns a distinct count of entries in the data for that particular week. 

    Added the similar formula for the outgoing orders but changed the column reference to Invoice date for outgoing orders.

    Hope this helps!

    Kind Regards,

    Pratyusa

     

  • 0
    Avatar
    Randy Brooks

    Pratyusa,

    Thank you so much!  That was a big help.  I created a table in a Klip and then hid all of the columns and plotted them over to a Bar Chart.  It is working perfectly EXCEPT I need to hide/ignore the values for the null week for those orders that don't have either an incoming or and outgoing date.  Is there an easy way to do this? Would I use a SELECT?  I'm still a noob, but I'm gradually getting up to speed.  Thanks, again!

  • 0
    Avatar
    Pratyusa Ray

    Hi Randy,

    Thanks for reaching out again.

    You can try the Filter option to Filter the null weeks.

    Hope this helps!

    Pratyusa

  • 0
    Avatar
    Randy Brooks

    Yes, that's what I ended up doing. Thanks again!

     

Please sign in to leave a comment.