Tableau Sum of Month Sales with Las Account Customer Item Sale of the month

Question

I am trying to return the sum of purchases in a month with the last sale of an account per a customer either using tableau calculations or sql.

I have data from August to January and each month has 100k plus accounts with multiple users in an account. The data set looks like below

 Account User Item  Plan        Status date     Sale
 1       a   toy    unlimited  open    1/1/2019    10  
 1       a   book   unlimited  closed  1/5/2019    10   
 1       a   toy    unlimited  open    1/10/2019   10 
 1       b   toy    unlimited  open    1/1/2019    10 
 1       c   book    unlimited  open   1/1/2019    10 
 1       d   toy    unlimited  open    1/1/2019    10 
 1       d   toy    unlimited  open    1/2/2019    10 
 1       f   toy    unlimited  open    1/1/2019    10 
 1       a   toy    unlimited  open    2/1/2019    10  
 1       a   book   unlimited  closed  2/5/2019    10   
 1       a   toy    unlimited  open    2/10/2019   10 
 1       b   toy    unlimited  open    2/1/2019    10 
 1       c   book    unlimited  open   2/1/2019    10 
 1       d   toy    unlimited  open    2/1/2019    10 
 1       d   toy    unlimited  open    2/2/2019    10 
 1       f   toy    unlimited  open    2/1/2019    10

Desired results for each account, customer, last sale and total month sales should look like

 1       a   toy    unlimited  open    1/10/2019   30 
 1       b   toy    unlimited  open    1/1/2019    10 
 1       c   book    unlimited  open   1/1/2019    10 
 1       d   toy    unlimited  open    1/2/2019    20 
 1       f   toy    unlimited  open    1/1/2019    10 
 1       a   toy    unlimited  open    2/10/2019   30 
 1       b   toy    unlimited  open    2/1/2019    10 
 1       c   book    unlimited  open   2/1/2019    10 
 1       d   toy    unlimited  open    2/2/2019    20 
 1       f   toy    unlimited  open    2/1/2019    10

I have tried tableau calculations using

IF [PlanCount] > 1 and [T|F MaxDate] = True then [Plan] else [Plan] END

OR Row_Number in hive but with any column count thats greater than 0, there is a Null Value in it wuth the max date cause the plan is set for maxdate but also null when the maxDate condition is false. Where should this be done, at the etl level or the tableau calculation level?

Gordon Linoff Gordon Linoff · Accepted Answer · 2019-03-02T11:58:53

In most databases, you can do use window functions like this:

select t.*
from (select t.*,
             sum(sale) over (partition by account, user, year(date), month(date)) as month_sales,
             row_number() over (partition by account, user, year(date), month(date) order by date desc) as seqnum
      from t
     ) t
where seqnum = 1;

The functions for extracting the year and month from the date might depend on what database you are actually using.

Tableau Sum of Month Sales with Las Account Customer Item Sale of the month

1 Answers