5 Replies Latest reply on Jun 14, 2018 6:53 AM by Kate Jones

# ZN Sum a subset of a column

Using sample data: I want to count up the number of records with a ship mode of same day. So, I created a new column that says if ship date = same day then 1 and added a sum to my table. Now, if there is no data it connects the line to the next time there is a sum >0 so I add ZN() to the formula. Yay! It shows 0s, but wait. If I click on the drilldown I get way more lines then the sum. So, the plan was to add ship mode to the marks color card. Sadly this prevents the same day count from reaching zero.

How can I use ZN() to pad data but have the view data only show the record that the sum is showing?

Things that I cannot do:

- Change the column dates to group by a time period (ex. weeks or month). This eliminates some formatting options I need

- Change the dates to discrete. I am using reference lines to pad the full timeframe when the data doesn't reach both ends of the time period.

• ###### 1. Re: ZN Sum a subset of a column

Hi Kate,

Not exactly sure, but does this work?

This still cannot show "0"

Thanks,

Shin

• ###### 2. Re: ZN Sum a subset of a column

Unfortunately no, without the zeros showing it gives the impression that there is an amount where none exists. For example, Jan 18 2015 now looks to have an amount between 1 & 3 even though the real sum is 0.

• ###### 3. Re: ZN Sum a subset of a column

Hi Kate,

Then, here is another approach.

Change the "Date" axis.

Thanks,

Shin

• ###### 4. Re: ZN Sum a subset of a column

1) Change your same day calculation to:

if [Ship Mode]="Same Day" then 1 else 0 end

2) Add a calculated field "MOD Same Day":

if { FIXED [Order date week]: sum([Same Day])}>0 then

if [Same Day]=0 then "Hide"

ELSE

"Show"

END

ELSE

"Show"

END

3) Add "MOD Same Day" to filters and use "Show" values only.

G

1 of 1 people found this helpful
• ###### 5. Re: ZN Sum a subset of a column

Thanks Garth! this was the solution I needed.