Need Help



  • Avatar
    Hamza Jap-Tjong

    Hi Dustin,

    Correct me if I did not got your question correctly;

    So what I understand is that you want to add a field to the datamodel which tells you if a specific member ID has 1 or >1 confirmation numbers. After that you want to know of those member ids that have multiple numbers, how many are unique.

    I think you can also solve this on the front-end in a pivot (however it does not make it possible to add a filter on dashboard level). In the Pivot-widget add:

    • member ID on a row level
    • DUPcount(confirmation number) on value level
    • count(confirmation number) on value level
    • DUPcount(confirmation number) - count(confirmation number) on value level
    • Filter on the last formula to only show >0 

    If you want to have a field (e.g. flag) to use on dashboard level, then it is neccessary to add this to the datamodel. You could add a custom query like;

    SELECT c.*, 
    when count(c.member_id) > 1 then 1
    else 0
    as Flag
    from confirmation_table c
    group by c.member_id

    You can replace 1 and 0 with Yes/No or True/False. Place them between '' 

    After this you can add the flag as dashboard filter. Create a widget that does a unique count on confirmation number. 

    Say you want multiple flags regarding uniqueness;

    SELECT c.*, 

    //Flags how many confirmations a member has
    case when count(c.member_id) > 1 then 1 else 0 end as Flag

    //Flags if that member_id has duplicates regarding conf.number
    case when count(c.confirmation_number) - count(distinct c.confirmation_number) = 0 then 0
    else 1 end as Flag_2

    from confirmation_table c
    group by c.member_id
    Comment actions Permalink
  • Avatar
    An Y

    Dustin Northrop  In your Widget formula I think you can do something like IF COUNT(ConfirmationNo) < DUPCOUNT(ConfirmationNo) , i.e. distinct count  less than total count, and return 1 or 0 . Can also filter rows by applying widget filter to check for that value > 0.


    Comment actions Permalink

Please sign in to leave a comment.