0 Replies Latest reply: Apr 21, 2016 8:47 AM by Sven Skottke RSS

    Comparing set to a subset

    Sven Skottke

      Hi everybody,

       

      It's my first question on this forum.

       

      So, what I'm trying to do is compare each value in my complete data set to a subset, to sum up how many distinct values are missing from that subset. This sum will then go into a stacked bar chart showing user statistics, as a measure.

       

      Here's the formula I wrote, so far:

       

      Sum(  

      If(

           UserName = //this should be from my full set

              If( //in this if-bloc I'd like to get a set of the users' names that did a transaction within a month. The month is a dimension on the chart

                 Aggr(Count(TransactionID), UserName, TransactionMonth) > 0

                 ,UserName //here the names should be returned

                 ,'0' //If there aren't any, return nothing

              )

              ,0 //if the current username from the full set matches any of the returned, then I don't want to count them

              ,1 //if there is no match, i.e. the user didn't do a transaction, increment the sum

          )

      )

       

      I have other measures for users who are in the month. But I can't use the same formula with different parameters, as the Aggr function only works with the available data; so I need to involve the full set to count how many are missing.

       

      Any help is appreciated.

       

      Thanks in advance,

       

      Sven