distinctCountOver between two periods of time

Q&A
, , ,
1

I have a calculated field that calculates Total Distinct name(users) per dashboard -

distinctCountOver(name,[dashboardname],PRE_AGG)

Then I want to calculate distinct name (users) per dashboard, but for a specified time period (between a start date and end date, ideally parameters(user based))

Finally, I want to calculate the utilization of the Dashboard by dividing these two calculated fields - Let’s say Total Distinct users are 10 for a dashboard over the full timespan, and for the last 20 days, the distinct users are 8, so the utilization for the last 20 days is 80%…

Can anyone help?

2

Hey @Chandan_Das ,

something like:

distinctCountOver(
       ifelse(
                 date>parameter1 AND date < Parameter2,
                 Name, NULL
                 )
      ,[dashboardname]
      ,PRE_AGG
)

And for the last part:

count(
      toString(
               extract(
                       'DD',
                        ifelse(
                               Date<Parameter1 AND Date>Parameter2,
                               Order Date, NULL
                                )
                        )
               )
        )
the first calculation/the second calculation*100

Hopefully this can help you. If not feel free to ask.
BR
Robert

1 Like