How to turn filtered data into hard data / delete specific cells?

Hi everyone,

So I have a dataset with budget per GL account, the problem is that the datasets contains 24 periods, 12 periods (1 to 12) with either 0 or a random number and 12 consistent periods (1 to 12) with the correct budget. Reference 1. in the screenshot below/

When merging dimensions and creating a detail that returns the budget, it obviously returns the #Multivalue error.

I tried to fix this by adding a =First measure, which lead to reference 2. in the ss below. I
I then created a dimension that equals the measure, but this returned no data in a different query.

What’s the best way to fix this, is there a way to delete just the 12 periods with inconsistent budgets?

How do you know which budget value is correct?

The consistent budget value is correct, so the 2.000 x12 periods should be the data that stays

I’m merging the GL account details to return the budget that’s relevant to the account. But the problem is that there’s 2 different budgets per period, period 1: 0 & 2.000 for example.

This causes the #MULTIVALUE error, because BO doesn’t know which of the two to retrieve. Hence the reason I’m asking for ways to delete the faulty budgets.

I’m thinking of creating a =MAX detail instead. I’ll try that first

First() Last() are functions for dimensions, use Min() Max() instead.

Budget is a a dimension, not a measure. Do you think that changing the qualification in Universe Builder can help with deleting the irrelevant budgets in the report?

if you want to avoid “Multivalue” you have to work/calculate with measures instead of dimensions.
if you want to find one relevant budget-value you can also work with mode() function to find the most frequently-occurring value

Hi Daniel,

Where do the wrong records of period 1 come from ? When I look at your table one, I can’t see anything that helps me to know which are the actual values for each period. So won’t BO. My advice would be to clean the budget data or dataflow behind to avoid having 0 or random value without any distinction from the actual values. Is there a flag or any descriptive field in the data provider that identifies the “dummy” values ?

Regards,
Chris

1 Like

Yes, It would and it would make the query faster. I assume that the budgets can be summarized. So the system is recording the budget as 0, but after that increased to the correct number. You will have to check what would happen to the data is that budget was first a higher value and gets decreased, if that will be recorded as a negative number (so the sum of all values is the actual budget), then the universe should be change. The budget should be changed to a measure with sum aggregation function

Fixed by something very dumb… We have two companies, that’s why it displayed two budgets per period. Filtering to one company, the right company, removed the multivalue error.