Tableau calculation to disregard filters

Question

My data looks like this:

endtimestamp|account|volume|pk|var1

jan-1, conosco, 123, 13231, a
jan-1, conosco, 123, 13231, b 
jan-1, conosco, 123, 13231, c 
jan-1, acme, 1323, 2123123, a
jan-1, acme, 1323, 2123123, b
jan-1, acme, 1323, 2123123, c
jan-2, conosco, 128, 131231, a
jan-2, conosco, 128, 131231, b
jan-2, conosco, 128, 131231, c
jan-2, acme, 1329, 31323, a
jan-2, acme, 1329, 31323, b
jan-2, acme, 1329, 31323, c
...

I have a calculation to get the sum based upon filter

Call it fixed_sum_volume

sum({FIXED [pk], [account]: max([volume])})

I have two filters

endtimestamp

account

When the date range Jan 1 is selected and the account ACME is selected

fixed_sum_of_volume will give me the sum of the date range - 1323

I need to divide this by the average of all time and i cannot figure out how to do this or if it is even possible with tableau.

Seems like it would be easy but i have been grappling with this for a while.

All in all, i am looking for:

avg(filtered volume)/avg(non filtered volume)

Yes it is possible with Tableau. You need one calculation to occur before the filter is applied, and a second calculation to occur after the filter is applied. There a few ways to control the timing of calculations and filters. Read about the “order of operations” in the tableau on-line help. — Alex Blakemore

Belayer Belayer · Accepted Answer · 2020-02-17T23:34:25

Well I'm not sure what sum(x)/avg(y) gives you but the below accomplishes it. BTW your value "fixed_sum_of_volume" given above is incorrect. You have 3 rows for 'acme' account for date 'Jan-1' each for 1323, the sum therefore is 3969.

with mytbl (endtimestamp,account,volume,pk,var1)  as
   ( values   (to_date('jan-1','mon-dd'), 'conosco', 123, 13231, 'a')
            , (to_date('jan-1','mon-dd'), 'conosco', 123, 13231, 'b')
            , (to_date('jan-1','mon-dd'), 'conosco', 123, 13231, 'c')
            , (to_date('jan-1','mon-dd'), 'acme', 1323, 2123123, 'a')
            , (to_date('jan-1','mon-dd'), 'acme', 1323, 2123123, 'b')
            , (to_date('jan-1','mon-dd'), 'acme', 1323, 2123123, 'c')
            , (to_date('jan-2','mon-dd'), 'conosco', 128, 131231, 'a')
            , (to_date('jan-2','mon-dd'), 'conosco', 128, 131231, 'b')
            , (to_date('jan-2','mon-dd'), 'conosco', 128, 131231, 'c')
            , (to_date('jan-2','mon-dd'), 'acme', 1329, 31323, 'a')
            , (to_date('jan-2','mon-dd'), 'acme', 1329, 31323, 'b')
            , (to_date('jan-2','mon-dd'), 'acme', 1329, 31323, 'c')
     )
select account, fixed_sum_of_volume, average_of_all_time, fixed_sum_of_volume/average_of_all_time "sum(filtered volume)/avg(non filtered volume)?"
  from 
       ( select m.account
              , sum(m.volume) filter (where to_date('jan-1','mon-dd') = m.endtimestamp) over() fixed_sum_of_volume
              , (select avg(m2.volume) from mytbl m2 where m2.account = 'acme' group by m2.account) average_of_all_time
           from mytbl m
          where m.account = 'acme' 
       ) a;

Tableau calculation to disregard filters

1 Answers