5 Replies Latest reply: Feb 10, 2018 8:45 PM by Sunny Talwar RSS

    Total function and set expression exclusions

    Jacob Wapinsky

      Hi all,


      I am trying to get a total count of products so that I can divide consumption to calculate a consumption per product.


      I want to display graphically versus month/year, product type, and day.  I do not want the count to change when I make a selection on the type of material I am trending.


      i.e. if I want to see the consumption per total products for material xyz, I want to be able to select xyz and not have the count of products change to only products where xyz is used. (if it is used it has a value, if it was not, it is null).


      here is the current code I am using to get the count of products.


      count({<[Material.Description]=,[File1.MaterialCode]=,[File2.MaterialCode]=, Material Code Master Key=,[File3.GroupCodeDescription]=,[File3.GroupCode]= ,[File4.ElementCode]=,[File4.ElementCodeDescription]=>}total <MonthYear,[ProductGroup],Day> Distinct [ProductId])


      I am using set expressions to try to prevent the count from changing based on material type selection.


      Here are the problems I am having

      1) If I do not use the Material code Key in my set expression or my filter pane, then the value changes when I make a selection on file1.materialcode

           (i.e. this month I have 30 products, when I don't include the key field and I select on File1.materialcode  i end up with 26 products)

      2) When I make a selection on File3.GroupCode, the heat count changes even though I am telling it not to in the set expression.

           (i.e. this month I have 30 products, when I make a selection on File3.groupcode I get 10 products)

      Ultimately I would like the count of products to be 30.


      Any Ideas on what i can do differently to get my desired result?