4 Replies Latest reply on Nov 8, 2017 2:31 AM by Norbert Maijoor

# Removing dummy data in calculations

Hi all,

I have a peculiar case while developing a dashboard. Basically my data contains few dummy records which I don't want to consider if there exists regular data however it should consider the dummy data if there are no other records. And this has to be per month. In short, if no records are here, use dummy data else ignore.

Currently the sheet looks like this -

Data for 3rd Column (99.54%)

Here the dummy record should not be considered as there is actual data.

 App Name End Time Month name Short Desc Start Time Types Duration Num of min Num of sec Value Some_name 8/2/2017 5:30 August Actual Data 8/2/2017 1:30 available 14,400 44,640 2,678,400 0.994623656 Some_name 8/1/2017 0:00 August dummy 8/1/2017 0:00 available 0 44,640 2,678,400 1 Some_name 8/29/2017 20:31 August Actual Data 8/30/2017 0:29 available 14,280 44,640 2,678,400 0.994668459 Some_name 8/22/2017 20:30 August Actual Data 8/23/2017 0:29 available 14,339 44,640 2,678,400 0.994646431 Some_name 8/15/2017 20:30 August Actual Data 8/16/2017 0:29 available 14,339 44,640 2,678,400 0.994646431 Some_name 8/8/2017 20:31 August Actual Data 8/9/2017 0:29 available 14,280 44,640 2,678,400 0.994668459 Some_name 8/1/2017 20:31 August Actual Data 8/2/2017 0:29 available 14,280 44,640 2,678,400 0.994668459

Data for 4th column (100%)

Here the dummy record should be considered, since it's the only data record

 App Name End Time Month name Short Desc Start Time Types Duration Num of min Num of sec Value Some_Name_2 8/1/2017 0:00 August dummy 8/1/2017 0:00 available 0 44,640 2,678,400 1

I am not sure how to approach this condition.

• ###### 1. Re: Removing dummy data in calculations

hi Shantam,

While I don't fully understand the logic around when you need to use a Dummy value and when to ignore it, you can probably amend the following logic to your exact rule. From your post I've assumed in Column 3 you don't want to use the dummy value as there is already a value for 8/1/2017 for that Store.

So the below formula, brings back the Actual Data for each Day/Store combination, and if it's NULL (i.e. No Actual Data)...then it returns the value

[Value to Use - LoD by Store/Day]

IFNULL

(

{FIXED DATETRUNC('day',[End Time]), [App Name]: SUM(IIF([Short Desc] = 'Actual Data',[Value],NULL))},[Value]

)

Hope that is what you were after, or can adapt the logic to your situation. Let me know if not

• ###### 2. Re: Removing dummy data in calculations

Hey Simon,

Thanks for the approach. In my case, Actual Data isn't the word "Actual Data". It varies.

Is there a way, we can modify IIF([Short Desc] = 'Actual Data',[Value],NULL)

to something like NOT 'dummy' ?

• ###### 3. Re: Removing dummy data in calculations

Yes we can do that!

[Value to Use - LoD by Store/Day]

IFNULL

(

{FIXED DATETRUNC('day',[End Time]), [App Name]: SUM(IIF([Short Desc] != 'dummy',[Value],NULL))},[Value]

)

• ###### 4. Re: Removing dummy data in calculations

Hi Shantam,

Find my alternative approach provided by Simon Runc as reference below and stored in attached workbook version 10.3 located at the original thread.

1.  Max Short Desc: {fixed [Month name]:max([Short Desc])}

2.  Min Short Desc: {fixed [Month name]:min([Short Desc])}

3. in account yes/no:

if [max Short Desc ]=[min Short Desc] and [Short Desc]='dummy' then str(DATEDIFF('hour',[Start Time],[End Time]))

elseif [max Short Desc ]<>[min Short Desc] and [Short Desc]='dummy' then "0"

else str(DATEDIFF('hour',[Start Time],[End Time]))end

4. % availability:

if attr([max Short Desc ])=attr([min Short Desc]) and attr([Short Desc])='dummy' then count([Short Desc])/sum({fixed [Month name]:count([Short Desc])})

elseif attr([max Short Desc ])<>attr([min Short Desc]) and attr([Short Desc])='Actual Data' then sum({fixed [Short Desc]:count([Short Desc])})/sum({fixed [Month name]:count([Short Desc])})

END

Regards,

Norbert

1 of 1 people found this helpful