Limit Filter

Comments

12 comments

  • Avatar
    Henry Pearce

    If you are using this plugin to pull back the most recent data for a given date does the fact table need to ordered by date or does Top 1 Date always mean most recent?

     

    Thanks

  • Avatar
    Takashi Binns

    Hi Henry, you can think of this as adding a subquery to your widget.  If you add a limit filter on a date field, it will query for the max (or min, if you use bottom) date value(s) first, then filter your fact based on this resultset.  So no need to order the fact table ahead of time.

  • Avatar
    Michael Slevin

    This doesn't seem to work on v6.6.1, Takashi can you confirm?

     

    The popup dialog does not work, and an error is shown in dev tools.

  • Avatar
    Yoni Lerner

    Works well for me on 6.6.1.12014

    But I see some issues with it on 6.7

  • Avatar
    Takashi Binns

    Hi Michael,

    I can see this plugin working fine on 6.6.1, maybe there a conflict with another plugin that you have installed.  Can you provide some more info on the error you are seeing?

    We have seen some issues with this plugin in 6.7 related to how the query gets handled, and are working on a fix.

  • Avatar
    Sharlene Carnegie (Edited )

    I'm seeing the following when attempting to use this functionality within a column chart widget.  It appears to work fine in a pivot table.  I am currently on 6.6.1.  Any ideas?

  • Avatar
    Takashi Binns

    Hi Sharlene,

    This plugin is actually designed to work with table and pivot table widgets, and not column charts.  The purpose of this plugin is to expand functionality when using multiple dimensions in the same widget and column charts are typically just 1 or 2 dimensions.  Can you provide some details on exactly what you're trying to accomplish with this?  Maybe a screenshot of what you're doing?

    thanks,

    -Takashi

  • Avatar
    Malinda Jepsen

    Takashi,

    Can this be used for two date values?  I'm hoping to use this to show a value at the beginning AND ending dates per row. In your example, it would be first unit price and last unit price (2 columns).  I haven't been able to figure out how to configure it to do that. We will also want to show the percent increase between the two columns. The first and end date is different for each row like your example.

    Malinda

  • Avatar
    Takashi Binns

    Hi Malinda,

    I don't think its possible to get the output you want with this plugin.  However, you can achieve this through measured value formulas.  To get the First Price, just create a formula for ( max( [Unit Price] , [Date] ) and use the advanced filter option to do something like this: 

    {
    "bottom": 1,
    "by": {
    "table": "Order Headers",
    "column": "OrderDate",
    "dim": "[Sales.OrderDate1 (Calendar)]",
    "datatype": "datetime",
    "merged": true,
    "level": "days",
    "agg": "min"
    },
    "custom": true
    }

    You can do the same thing for the Most Recent Price, but do this instead

    {
    "top": 1,
    "by": {
    "table": "Order Headers",
    "column": "OrderDate",
    "dim": "[Sales.OrderDate1 (Calendar)]",
    "datatype": "datetime",
    "merged": true,
    "level": "days",
    "agg": "max"
    },
    "custom": true
    }


    Now you can save these as starred formulas, and just create a third formula that calculates the % change between them

  • Avatar
    Jessica Orlando

    Hi Takashi,

    I am trying to filter an indicator widget to only display a metric for the max date. I copied the formula in the image above, but could not get it to work. I kept receiving 'Invalid query syntax' when testing it.

    This is what I am running. I was unsure what 'Order Headers' referenced and why you had '(Calendar)' in the dim row.

    {
    "top": 1,
    "by": {
    "table": "CapitalAccountSummary",
    "column": "InvestorCapitalAcctDate",
    "dim": "[CapitalAccountSummary.InvestorCapitalAcctDate]",
    "datatype": "datetime",
    "merged": true,
    "level": "days",
    "agg": "max"
    },
    "custom": true
    }

     

    Thanks!

     

  • Avatar
    Admin

    Hi Takashi,

    Are there plans to make it work with 7.2?

    Can you tell me if this is possible since I can't test it:

    I have a dynamically changing form that sometimes can have one date in it and sometimes multiple. To differentiate them I have a Caption field. The structure looks like this:
    Caption     Value-Date
    Date1       01-01-2000  
    Date2       02-01-2000
    Date1       03-01-2000

    What I would like to achieve is pull this into a Table widget in the way that for each column containing Value-Date I want to filter them using the Caption field so Value-Date(1) displays Date1 Caption dates, Value-Date(2) displays Date 2 Caption dates.
    ID    Value-Date(1)   Value-Date(2)
    1      01-01-2000              -
    1             -                 02-01-2000

     

    Thanks for your help,

     

    Pawel

  • Avatar
    Hendrik Venter

    I've tried using this in v7.3, it also doesn't seem to work.

    Other analytics tools makes it so easy to simply display the last 10 records based on date-time, this is something that's still lacking from Sisense. 

Please sign in to leave a comment.