Forum Discussion

zach_myt's avatar
zach_myt
Data Pipeline
09-19-2023

All days in date not working

I have a count of unique values and use (value,ALL(days_in_date)) function but is still being affected by my blox date filter. I need a way to calculate an all time formula for one column that is not affected by the date filter. Any ideas?

This is what I get when the filter is off in the right hand pane.

 

This with the filter turned on but the ALL() clause on patient user ID.

 

From the second set using ALL() I am missing Blood Disorders and Coronavirus. 

Department name is being pulled from a dimension table and the formula is from another table.

 

Blox
dashboards & reporting
Data Models

3 Replies

Replies have been turned off for this discussion
  • irismaessen's avatar
    irismaessen
    Data Pipeline
    09-20-2023

    All([days in date]) only works as pretty much expected if your date filter is on 'day' granularity. If your filter is on months, weeks, quarters or years, your data is still getting filtered. So you should add more all() clauses to your formula for any granularity likely to happen to your date filter.

    Iris

  • zach_myt's avatar
    zach_myt
    Data Pipeline
    09-20-2023

    After playing with it some more it seems the date filter is really on the Department column and for now I cannot figure out a way to list all departments since you cannot do ALL() on a non aggregate field. The overall goal would be to have all departments listed then a  column for Patient User ID that is affected by date filter then another column for All Time Patient User ID that is not affected by date filter and always shows the entire count.

    • irismaessen's avatar
      irismaessen
      Data Pipeline
      09-22-2023

      Another thing you can try is to force (one of )  the measures to use a value from the table containing the 'Department' column. That could also be a dummy number added in data prep (add a column of 1's). Or if your Department names are unique, you can use their distinct count.

      So something like  <your formula>*MAX(dummy column) or <your formula>*COUNT(department). Both of these should multiply by 1, resulting in the actual value of your formula. But now the formula has a component from your columns table.
      Or if you really want to be sure the number doesn't have an *actual* influence: <your formula> + 0*Count(department)