How do I use sum inside of nested IF in Tableau?

Question

I want to calculate how many shopping points a customer would get based on the amount of spending and the date of their membership. Some customers order more than once, hence the nth.order column, but I want to use the total spending regardless how many times they order.

They will get 1 point for every 1.000.000 of total spending. During special season (6/1/2021 - 8/31/2021), they will get get triple point but only if their total spending is minimum 3.000.000.

It's also rounded down by 1.000.000 significance. So total spending 4.500.000 during normal season will get 4 points, and if their membership is in special season then they will get 12 points.

Here's my sample data :

cust.id	start.date	end.date	nth.order	spending
123	6/5/2021	6/7/2021	1	500.000
123	6/5/2021	6/7/2021	2	500.000
456	6/8/2021	6/9/2021	1	3.000.000
789	6/11/2021	6/13/2021	1	1.500.000
789	6/11/2021	6/13/2021	2	1.500.000
789	6/11/2021	6/13/2021	3	1.500.000
000	4/1/2021	5/5/2021	1	3.500.000
000	4/1/2021	5/5/2021	2	3.500.000

Here's how I want the result to be :

cust.id	start.date	end.date	spending	poin
123	6/5/2021	6/7/2021	1.000.000	1
456	6/8/2021	6/9/2021	3.000.000	9
789	6/11/2021	6/13/2021	4.500.000	12
000	4/1/2021	5/5/2021	7.000.000	7

cust. 123 get 1 point because their total spending is 1.000.000 but not minimum 3.000.000 even though it's in special season.

cust. 456 get 9 because their total spending is >= 3.000.000 and it's in special season

cust.789 get 12 because of the same reason as cust.456

cust.000 only get 7 because even if the total spending is more than 3.000.000, but it's not during special season

I have tried this :

IF start.date >= #6/1/2021#
AND end.date <= #8/31/2021#
AND sum[spending]>=300000
THEN FLOOR([spending]/1000000)*3

ELSEIF start.date>= #1/1/2021#
AND end.date <= #12/31/2021#
THEN FLOOR([spending]/1000000)

ELSE 0

END

but the result that I get is:

cust.id	start.date	end.date	spending	poin
123	6/5/2021	6/7/2021	1.000.000	0
456	6/8/2021	6/9/2021	3.000.000	9
789	6/11/2021	6/13/2021	4.500.000	3
000	4/1/2021	5/5/2021	7.000.000	6

I tried to do this:

IF start.date >= #6/1/2021#
AND end.date <= #8/31/2021#
AND sum[spending]>=300000
THEN FLOOR(sum[spending]/1000000)*3

ELSEIF start.date>= #1/1/2021#
AND end.date <= #12/31/2021#
THEN FLOOR(sum[spending]/1000000)

ELSE 0

END

but it keeps saying "cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions". I have tried to create calculation field that contains sum[spending], named it "total spending" and did something like ...THEN FLOOR ([total spending]/1000000)... but it gives the same error message.

Please help..

Fabio Fantoni · Answer 1 · 2021-09-07T12:50:26.480

Since your start/end dates are always related to the customer ID, the different order rows can be fully aggregated.

That being said, you may want to create a Total Spending:

{ FIXED [Cust.Id] : SUM([Spending])}

Then you need to aggregate your data in order to see if the membership is in the special range with Promotion:

{ FIXED [Cust.Id] : MAX( if [Start.Date] >= MAKEDATE(2021,6,1) and [End.Date] <= MAKEDATE(2021,8,31)
                         then 'Y'
                         else 'N'
                         end
                       )
}

Then you can treat those aggregated data (at Customer level) without any concern with Points:

floor([Total Spending]/1000000)
*
if [Promotion] = 'Y' and [Total Spending] >= 3000000 then 3 else 1 end

The result will look like this:

How do I use sum inside of nested IF in Tableau?

1 Answers1