How can I apply limit filter to the timeDimension property of cubeJs?

Question

Below is the scenario where I'm trying to apply a limit of 2 using cubejs. Here is the json query for this :

{
    "measures": [
        "actors.actorscount"
    ],
    "timeDimensions": [
        {
            "dimension": "actors.createdAt",
            "granularity": "month",
            "dateRange": "Last quarter"
        }
    ],
    "dimensions": [
        "actors.genre"
    ],
    "filters": []
}

And after applying limit=2 to below query, I'm getting below bar-graph response:

{
        "measures": [
            "actors.actorscount"
        ],
            "timeDimensions": [
                {
                    "dimension": "actors.createdAt",
                    "granularity": "month",
                    "dateRange": "Last quarter"
                }
            ],
                "dimensions": [
                    "actors.genre"
                ],
                    "filters": [],
                    "limit":2
    }

and the current bar-graph I'm getting as :

Expecting bar-graph response after applying limit :

so, what should be done to get the correct bar-graph response?

score 2 · Accepted Answer · answered Feb 02 '20 at 05:12

Such result can be achieved in 2 steps:

First to get top 2 dimensions without time granularity:

{
    "measures": [
        "actors.actorscount"
    ],
    "timeDimensions": [
        {
            "dimension": "actors.createdAt",
            "dateRange": "Last quarter"
        }
    ],
    "dimensions": [
        "actors.genre"
    ],
    "order": { "actors.actorscount": "desc" },
    "limit": 2
}

Then use two top dimensions from first query result to get desired chart:

{
    "measures": [
        "actors.actorscount"
    ],
    "timeDimensions": [
        {
            "dimension": "actors.createdAt",
            "granularity": "month"
            "dateRange": "Last quarter"
        }
    ],
    "dimensions": [
        "actors.genre"
    ],
    "filters": [{
      "dimension": "actors.genre",
      "operator": "equals",
      "values": ["action", "comedy"]
    }]
}

Ashish Bhardwaj · Answer 2 · 2020-01-30T11:08:55.790

0

The dimensions array contains only one value inside but it is having a comma after that value which is the wrong syntax. You have to fix this. (Remove the comma and your code will work).

Please remove the "limit" key-value pair from "timeDimensions" array and add it as independent key-value pair not under some property.

edited Jan 30 '20 at 11:08

answered Jan 30 '20 at 10:34

Ashish Bhardwaj

158
5

Thanks, Ashish, I tried your solution but it won't work. getting following error :Error: Invalid query format: child "timeDimensions" fails because ["timeDimensions" at position 0 fails because ["limit" is not allowed]]@Ashish Bhardwaj – prasanna sulakhe Jan 30 '20 at 10:43
I think you have to define "limit" outside of "timeDimensions" array as an independent key-value pair. It should not come under timeDimensions. – Ashish Bhardwaj Jan 30 '20 at 11:06
We had already done that , but it applies to parent data and I need it on child data, as in, here I need a limitation on genre(action, drama, comedy, thriller, romance) for all months. – prasanna sulakhe Jan 30 '20 at 12:28

How can I apply limit filter to the timeDimension property of cubeJs?

2 Answers2