Hello,
I'm reaching out for some assistance. I've put a sample workbook in this post.
I have three fields in an excel spreadsheet... Date, First Count and Second Count
I've created calculated fields to get Yesterday Count and the MTD Count.
I then created a Daily Calc field to divide Yesterday First Count by Yesterday Second Count
I tried creating a MTD Calc field to divide MTD First Count by MTD Second Count. This showed daily values when I had the Date as a discrete field in the Row column. However, when I tried to pull the Date so that I would get a monthly total, the field became null.
Even though the last step didn't work, I moved forward and created the next step to create a % Change field where the calc is Daily Calc - MTD Calc / MTD Calc to see what the percentage change is.
Ultimately, if this is a positive change, I'd like it to show green with a green triangle next to it to show it increased. If negative, show red with a red upside down triangle to show a decrease. I have not moved on to this step.
Everything stated in this text should match the attached Tableau workbook. The first sheet shows that the calcs are running when the Date is in the Row field. The second sheet, I simply removed the Date from the row field and MTD Calc and % Change are now null. Ultimately, I'm looking to use the 2nd sheet without Date in the Row field.
Any assistance for completing these steps would be truly appreciated.
Hi Brian
I've resolved this issue in your workbook (solution attached).
The basic calculation is:
MTD First Count = IF ([Date] < TODAY()) AND DATETRUNC("month",[Date]) = DATETRUNC("month",TODAY()-1) THEN [First Count] END
(Note that DATETRUNC is easier than comparing months and years separately, and you don't need "ELSE NULL" because it's the default).
MTD Second Count is similar.
Then: MTD Calc new = SUM([MTD First Count])/SUM([MTD Second Count])
Dan
