I am unable to share a workbook at the moment, but I can give a general picture of what I have going.
I need to create a table that will count how many line items are coming in per week and categorize them as New, Pending, or Complete. It will look something like the excel table attached. Unfortunately, I cannot get it to show me what items came in as NEW in previous weeks. It will only generate new for the current week.
Formula for grouping:
IF [Status] = 'WIP' THEN 'Pending'
ELSEIF [Status] = 'Incorporated' THEN 'Complete'
ELSEIF [RedDate] > DATEADD('day', -7, today()) THEN 'New'
ELSE 'Pending'
END
Problem I'm having is that as soon as I apply the Week Ending formula , it stops giving me a count in the row for NEW on previous weeks data.
***Week Ending formula (our week starts on Sun & and ends on Sat)
Date(DATETRUNC('week',[RedDate],'Sunday'))+6****
I need to see how many line items were in each category per week, by week ending.
I also need to create a table/chart that will show what the increase or decrease was from the previous week so we can determine if items are being completed within a 7 day turnaround time.
Can anyone help me :(
Hi Michelle,
I figure you are experienced enough to figure out the rest of the code, so I'll just work on the problem bit. Here's what I think might work;
Step 1 - create datetrunc week. This will truncate your date field into relevant weeks. DATETRUNC('week',[RedDate],'Sunday') should do the trick. You will use this in the view instead of date, as by extension this is what the pending, complete and new values are calculated against.
Step 2 - Inside your main calculation, replace the today() statement with your truncated week clause - this will change your logic from just grabbing the last 7 days into the weeks.
Step 3 - Adjust your calc - I think it might work already as the ELSEIF looks like its just doing a comparison of the last 7 days to the comparison date - once you are feeding it the datetrunc week value, it should work with an italic should.
Step 4 - celebrate the win.
HTH
Peter
