Controlling Null Values without Impacting Totals

Q&A
, , ,
1

I have a table visual grouped by several parameter controls. The formulas for period 1 and period 2 looks like this:

ifelse(

${MeasureSelect} = ‘Dollars’,

sumIf(SalesTotal, in(depletionmonth, ${P1DateInt})),

${MeasureSelect} = ‘Cases’,

sumIf(Cases, in(depletionmonth, ${P1DateInt})),

The date parameters are integers we link to the dataset. The issue I’m having is the table displays products with null sales for selected period as long as there has been sales in a prior period. I’ve tried null handling with a filter like :
ifelse(isNull({Period 1}) AND isNull({Period 2}), 0, 1) but this impacts totals for other metrics i’m using like rolling 12 month sales. Is there a way to control for this without impacting other metrics?

image
image1274×372 35.9 KB

2

Hi @dspringer,
This may be somewhat dependent on how your dataset is structured or other variables in affect, but as a work around, what if you tried setting up a filter around an extreme number that won’t come into play, so that you can use the ‘exclude nulls’ option (this way the only thing affectively being filtered out are nulls:

image
image182×514 15.1 KB

3

Hi @dspringer

It’s been a while since we last heard from you. If you have any further questions, please let us know how we can assist you.

If we don’t hear back within the next 3 business days, we’ll proceed with close/archive this topic.

Thank you!

4

Hi @dspringer

Since we have not heard back from you, I’ll go ahead and close/archive this topic. However, if you have any additional questions, feel free to create a new topic in the community and link this discussion for relevant information.

Thank you!