Trying to create a measure to show MTD & YTD sales.
This formula works for MTD Sales:
IF [DOCDATE] <= TODAY() AND DATEDIFF('month',[DOCDATE], TODAY()) = 0
THEN [SALESAMT]
END
BUT, I need it to be based on the last date in the data group.
Sometimes want to filter data based on Date Range: AS in last July 2013 info
I get an error about mixing aggregated & non-aggregated functions.
When I use the following formula:
IF [DOCDATE] <= TODAY() AND DATEDIFF('month',[DOCDATE], MAX([DOCDATE])) = 0
THEN [SALESAMT]
END
Any Ideas
In your original calc, nothing was aggregated so everything went smoothly. When you added MAX() to the second formula, you aggregated part of the calc, so Tableau wants everything aggregated so it can compute properly.
I have no way to test, but you can try:
IF attr([DOCDATE]) <= TODAY() AND DATEDIFF('month',[DOCDATE], MAX([DOCDATE])) = 0
THEN sum( [SALESAMT])
END
I could be missing something else, as well.