3 Replies Latest reply on Jul 10, 2018 12:06 PM by Deepak Rai

# How do you fill in missing data to display zeroes, instead of not showing a value at all?

I am trying to display all values between the lowest and highest value, so that they can be displayed as zeroes in a viz. In the attached workbook, I need to display a value for 3, 6, 7, 8, and show zeroes for those values.

Background on the data: I have user_ids that visit a website, so there is a visit_date and visit_count:

 user_id visit_date visit_count 1234 6/21/2018 1 1234 6/2/2018 1 1234 6/15/2018 3 5554 7/9/2018 1 5554 6/25/2018 1 5554 6/15/2018 1 5554 6/16/2018 1

I created two calculations:

1.) c_Total Visit Count for User ID = {FIXED [User Id] : SUM([Visit Count])}    <<< This gets converted to a discrete dimension, because I want to 'bucket' the member counts by these values.

2.) c_Distinct User ID Count = COUNTD([User Id]) <<< This is the measure getting 'bucketed' by the Total Visit Count.

Thanks!

Thanks

Deepak

• ###### 2. Re: How do you fill in missing data to display zeroes, instead of not showing a value at all?

Thanks Deepak, can you explain the solution and it's application? There are a number of functions here that are rather dense:

Calculation 1 = IFNULL(LOOKUP(SUM([c_Distinct User ID Count]),0),0)

Calculation 2 = INDEX()

Calculation 3 = {FIXED [c_Total Visit Count for User ID]:SUM([c_Distinct User ID Count])}

Calculation 4 = IFNULL(SUM([Calculation3]),0)

Calculation 5 = IFNULL(LOOKUP([Calculation4],0),0)

Thank you

• ###### 3. Re: How do you fill in missing data to display zeroes, instead of not showing a value at all?

I tried everything, I Changed your Axis to continuous to show you missing Values and Tried Different Tricks to Bring 0 at 3,6, 7, 8, but looks like I can't do that. So These are all those Calcs. Will Try later though.

Thanks

Deepak