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

    Removing dummy data in calculations

    Shantam M

      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 NameEnd TimeMonth nameShort DescStart TimeTypesDurationNum of minNum of secValue
      Some_name8/2/2017 5:30AugustActual Data8/2/2017 1:30available14,40044,6402,678,4000.994623656
      Some_name8/1/2017 0:00Augustdummy8/1/2017 0:00available044,6402,678,4001
      Some_name8/29/2017 20:31AugustActual Data8/30/2017 0:29available14,28044,6402,678,4000.994668459
      Some_name8/22/2017 20:30AugustActual Data8/23/2017 0:29available14,33944,6402,678,4000.994646431
      Some_name8/15/2017 20:30AugustActual Data8/16/2017 0:29available14,33944,6402,678,4000.994646431
      Some_name8/8/2017 20:31AugustActual Data8/9/2017 0:29available14,28044,6402,678,4000.994668459
      Some_name8/1/2017 20:31AugustActual Data8/2/2017 0:29available14,28044,6402,678,4000.994668459

       

       

      Data for 4th column (100%)

       

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

                

      App NameEnd TimeMonth nameShort DescStart TimeTypesDurationNum of minNum of secValue
      Some_Name_28/1/2017 0:00Augustdummy8/1/2017 0:00available044,6402,678,4001

       

       

      I am not sure how to approach this condition.

        • 1. Re: Removing dummy data in calculations
          Simon Runc

          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
            Shantam M

            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
              Simon Runc

              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
                Norbert Maijoor

                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