Line chart in relation to zero values

NathanDorsch

I just noticed a line chart I have has the lines connected directly to the next available date with data... which is misleading because it makes it seem as if there is data on the dates in between.

In the screenshot below, the value for 1/31 is 4 and 2/13 is 2.... however 2/1-2/12 is 0, but if you're looking at the line chart, it would seem like each day has 2-4.

Is there a way to set it up so the line will to go zero on 2/1 and back up to 2 on 2/13?

GrantSmith

Hi @NathanDorsch

Do you have specific records with 0 for that day or are those days missing in your dataset? If you have the dates in the dates in the dataset with a 0 value it should appear as 0 and drop down to 0 in your graph.

NathanDorsch

I don't have specific records with 0... they're missing. So in my case, is there a way to address this (without injecting data with 0 for each day which I really don't want to do....)?

GrantSmith

Domo is only going to plot the data you have so you'd need to include those dates. You can utilize the Calendar dataset from the Domo Dimensions connector and left join your dataset to that based on the date as it'll have all the dates for you already. Then you just use the new date field from the dimensions dataset instead of your dataset and everything else can stay the same. It will then have your values be NULL which won't graph anything on your chart but will cut the line so it won't overlap those dates.

NathanDorsch

ughhh... I don't think this will be feasible with my dataset... it's massive with all sort of columns of data - so I'd have a ton of combinations to add..

NathanDorsch

I'm now working on another dataset with a similar issue... basically, I have a bunch of data for cumulative reviews on each product. However, if there were no reviews for a particular product on a specific date, my dataset will not show the cumulative review for that product.... so for example, I may have the following data for Product A - in which I'm missing data in between 1/7-1/9 (because there were no reviews posted on those days) and the cum. rating should be 3.95.

Can you advise how I can do this in an ETL? I'm thinking I may need to use Rank & Window and reference the next actual value to populate the fields downward....

Date -- Product -- Cum. rating -- Cum. reviews -- Brand -- RPC

1/5/21 -- product A -- 3.93 -- 845 -- Ridgid -- B23398

1/6/21 -- product A -- 3.95 -- 846 -- Ridgid -- B23398

1/10/21 -- product A -- 3.99 -- 847 -- Ridgid -- B23398

GrantSmith

1. Import the Domo Dimensions - Calendar Dataset
2. Within your ETL:

• Left join you data to this calendar dimensions dataset
• Take this output from the join and feed it into a rank and window function
• COUNT your RPC OVER / Partitioned by the product and ORDER BY Date - This will get us a group number - essentially tells us when there's a non-null value but increasing the count - call this new field grouper
• Feed that into another Rank and window function
• Take the MAX of your rating and review fields over / partitioned by the product and the new grouper field. This will be your forward filled value.