1 Reply Latest reply on Feb 4, 2019 4:07 AM by Jim Dehner

# Aggregate Function with NULLs - A Better Way?

Hello,

I have a data related to (for example) a Dentists office.

There are 2 KPIs I'm interested in tracking and accumulating Year to Date (YTD) - for now though I've just calculated the full year 2018

The data are as follows:

 Description KPICode Jan Feb Mar Apr May June July Aug Sept Oct Nov Dec Existing Clients Seen KPI01 174 184 162 166 189 179 122 167 194 NULL 253 185 New Clients Seen KPI02 19 40 29 26 36 45 39 64 50 NULL 40 32

To calculate the full year I have used the following calculated measure:

SUM(IIF([KPICode] = "KPI01", zn([Jan])+zn([Feb])+zn([Mar])+zn([Apr])+zn([May])+zn([June])+zn([July])+zn([Aug])+zn([Sept])+zn([Oct])+zn([Nov])+zn([Dec]),0))

+ SUM(IIF([KPICode] = "KPI02",zn([Jan])+zn([Feb])+zn([Mar])+zn([Apr])+zn([May])+zn([June])+zn([July])+zn([Aug])+zn([Sept])+zn([Oct])+zn([Nov])+zn([Dec]),0))

What would be a more elegant way of doing this same calculation?

• ###### 1. Re: Aggregate Function with NULLs - A Better Way?

Shane

your data should be pivoted around dates to create a dimension Pivot Data from Columns to Rows - Tableau

Leave the KPI out of the pivot and deal with it separately in the aggregation (min(), or avg()

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.