Cube filters with the time dimension are not working

[nhannt201]

Describe the bug I use the timeDimension by year, and then I also apply filters with 'notEquals,' where the member still belongs to the same field as the timeDimension. Now, after querying, the returned data is in 'all' format, but the filters have not been applied. I have tried with 'equals,' and it works normally.

To Reproduce Steps to reproduce the behavior:

1. Select timeDimention by Year
2. Select measure sum number
3. Select dimensions string
4. Run build in playground

Expected behavior The returned results are as if the filters have not been applied.

Screenshots

Minimally reproducible Cube Schema

{
  "dimensions": [
    "tbl_lbklptj2d7s_6gl1j.AreaName"
  ],
  "order": {
    "tbl_lbklptj1wzh_ng7yt.Product_Price_sum": "desc"
  },
  "limit": 100,
  "measures": [
    "tbl_lbklptj1wzh_ng7yt.Product_Price_sum"
  ],
  "timeDimensions": [
    {
      "dimension": "tbl_lbklptj1wzh_ng7yt.DateKey",
      "granularity": "year"
    }
  ],
  "filters": [
    {
      "member": "tbl_lbklptj1wzh_ng7yt.DateKey",
      "operator": "notEquals",
      "values": [
        "2017-01-01"
      ]
    }
  ]
}

Version: [0.32.3]

[igorlukanin]

Hi @nhannt201 👋 Let me explain how notEquals works. It applies the filter to dimension values before they are truncated to selected granularity. Please see the following example.

Consider the following cube:

cubes:
  - name: dates
    sql: >
      SELECT 1 AS id, '2020-01-01T00:00:00.000Z' AS timestamp UNION ALL
      SELECT 2 AS id, '2020-01-02T00:00:00.000Z' AS timestamp UNION ALL
      SELECT 3 AS id, '2020-01-10T00:00:00.000Z' AS timestamp

    dimensions:
      - name: timestamp
        sql: timestamp
        type: time

    measures:
      - name: count
        type: count

If you query it without filters and using the weekly granularity, you get the following result:

If you apply the filter to the original dimension values, you get some results filtered out:

You can also see that the "filtered out" time dimension values are not exactly the ones I specify in the filter. I hope it makes sense and explains what is happening here.

In your use case, please consider specifying the date range in your time dimension or using the notInDateRange filter.