Filter by Aggregation Function

Comments

5 comments

  • Avatar
    Felippe Caso

    I've also tried to use a JAQL filter, without success. My last attempt is as follows:

     

    {
    "datasource": "MG - Orders",
    "metadata": [
    {
    "jaql": {
    "dim": "order_dim.seller_id",
    "filter": {
    "and":[
    {
    "filter":{
    "measure": {
    "dim": "order_detail.total_amount",
    "agg": "sum"
    },
    ">=": 2000
    }
    },
    {
    "filter":{
    "measure": {
    "dim": "order_dim.order_date",
    "level": "days"
    },
    "last":{
    "count": 50
    }
    }
    }
    ]
    }
    }
    }
    ]
    }

    0
    Comment actions Permalink
  • Avatar
    Michael Becker

    Hi Felippe,

    I would create a Custom SQL like that:

    SELECT id, SUM(total_amount) FROM order_detail

    GROUP BY id

    this gives you total sales per id, than you can create custom field on your seller table and use LOOKUP function to get that total for every seller. THis way you will get a fixed value per seller, which you can use in your front end formula as a third parameter. Hope it helps.

    Regards,

    Michał Becker

    0
    Comment actions Permalink
  • Avatar
    Elliott Herz

    Hey Felippe,

     

    You can add a secondary value to your widget for the other aggregation, then you can filter off of this additional metric, and once complete, you can delete the secondary (keep it as a widget filter though). So actually in this approach, you would probably want to build it as a pivot table to start, and once everything is filtered fine, convert to an indicator.

     

    Another approach, is to use a CASE statement within the aggregation. So using multi-pass aggregation you can do something like this:   SUM([Id], CASE WHEN SUM([Total_Amount]) > 2000 THEN 1 ELSE 0 END) )

    So in this case your not filtering the data out, but setting it to 0 when you aggregate it.

     

    Hope this helps.

    Best, Elliott

    0
    Comment actions Permalink
  • Avatar
    Felippe Caso

    Hi Elliott and Michał,

    Thanks for your quick response.

    Both the custom table and the CASE implementation seem to do the work fine!

    I'll probably try the CASE first, since I'll also have to look at the evolution for this metric, thus the "last 60 days" condition would have to be applied dynamically.

    Do you know if it would still work on this case? I wouldn't use order_date as the axis for this evolution, right?

    Best,

    Felippe

    0
    Comment actions Permalink
  • Avatar
    Elliott Herz

    Yeah, case statement is probably the best approach, should work for you. Last 60 days can be a dynamic dashboard filter, or a hardcoded widget filter, and would react the same to filtering the indicator.

    Best, Elliott

    0
    Comment actions Permalink

Please sign in to leave a comment.