4 Replies Latest reply: Dec 13, 2017 5:53 AM by Tim Poismans RSS

    From if statement in table to measure

    Tim Poismans

      Hey all,


      I recently encountered a problem involving a lot of filtering through set analysis.

      To make things easier, I simplified the filtering based on priority level.


      A little clarification:

      A client has 10 "super articles". There's a seperate view for each article.

      Now, the client sells items. And items are built up out of articles, including super articles.

      The problem was: under which super article do we show an item when it's built up out of more than one super article.


      I made 4 priority levels. In the application, the field is called "Filter weight".

      Now based on those levels, I can decide which item is shown under which super article.

      The super articles with priority(Filter weight) = 1 don't require extra filtering as they need to be shown under their respective super article.

      When an item is built up out of super articles with priority 2, 3 or 4, I do require extra filtering.

      The extra filtering is done as such:


           If(not WildMatch(Aggr(Concat([Filter weight]),Item),'*1*')>0,

                If(WildMatch(Aggr(Concat([Filter weight]),Item),'*2*')>0,Item)


      This is for a super article which belongs to priority level 2.


           If(not WildMatch(Aggr(Concat([Filter weight]),Item),'*1*')>0 AND not WildMatch(Aggr(Concat([Filter weight]),Item),'*2*')>0,

                If(WildMatch(Aggr(Concat([Filter weight]),Item),'*3*')>0,Item)


      This is for a super article which belongs to priority level 3.


      The filter is used on the dimension Item.


      In my tables there's no problem using this type of filtering for the items I don't want to see.

      However, I don't seem to be able to incorporate this in a measure NOT in a table, which is due to there not being a structure anymore. I tried to solve this with the Aggr function, but it seems I can't figure it out.


      An example:

      For Order 220150, we've got Item 40. Item 40 is built up out a number of articles, of which 3 super articles which belong to different priority levels.

      Superartikel Filter 2.PNG

      Through the filter for a super article which belongs to priority level 2, I can determine it is shown under the correct super article.

      The filter is done based on a Concat of the filter weight of all the articles(products) under the item:

      Superartikel Filter 3.PNG


      What I want to achieve is that I can make the Sum(# Net Amount) based on this filterting.

      Superartikel Filter 1.PNG

      Which works fine in the table above, as we got a structure of Document Number ==> Item ==> Product.

      But when I want to make a measure for the Sum(# Net Amount) for a super article belonging to priority level 2 in a KPI, I can't seem to get it to work through an Aggr function.


      Is it possible to edit the filtering so it's usable in a measure outside of a table?



      P.S.: As attachement, I've put an Excel with the data shown in the screenshots.