0

I am trying to get the below table in Tableau.

week XX (previous week) Month X (prev.Month) YtD
20,000 100,000 400,0000

I am trying to compare the previous week with the prev month. So far I have got the following:

Firstly, I got the day on which the full week ended on, i.e., Sunday as per below

(MAX(IF DATEPART('weekday',DATE([Order Date & Time]))=1 and date([Order Date & Time]) <TODAY() 
then DATE([Order Date & Time])

END))

Next I am trying to get the Sales - Last full week using the following:

IF DATEDIFF('week',DATE([Order Date & Time]),[Last Complete Sunday]) = 0 THEN COUNTD([Sales]) END

I am getting the following error: Cannot mix aggregate and non-aggregate arguments with this function

the problem is in the datediff() because when also trying to work it on it's own without the IF it still gives me same error. I cannot understand why because I am converting [Order Date & Time] to Date and Last Complete Sunday is a date.

As reference used: https://onenumber.biz/blog-1/2021/6/8/how-to-compare-last-full-week-to- previous-full-week-in-tableau Can someone help please?

alphasqrd
  • 53
  • 6

1 Answers1

0

The problem is fixed by putting your IF statement INSIDE the COUNT.

COUNTD(
 IF DATEDIFF('week',DATE([Order Date & Time]),[Last Complete Sunday]) = 0 THEN [Sales] END
)

If it doesn't match the if statement it will return NULL and will not be in your distinct count.

Matthew Walkup
  • 76
  • 5