1 Reply Latest reply on Jul 25, 2016 1:54 PM by diego.medrano

    How to count different account category created using INCLUDE LOD expression

    SHIVNATH SHAW

      I have a data in the following format…

       

      Account IdProductQtrsales
      AXFY17-Q2200
      BXFy16-Q4250
      BXFY17-Q130
      AYFY16-Q340
      CZFY16-Q3100

      I have to group the accounts based on the following rule

      1. If the account’s sales 3qtr ago is positive but zero in last 2 quarters. And also zero sale current qtr then it is in group1
      2. If the account’s sales 3qtr ago is positive but zero in last 2 quarters. And positive sale in current qtr then it is in group2
      3. Else other group

      Please note user can select one/more product from the quick filter in the view.

       

      Want to know how many accounts are in Group1, Group2 and others.

       

      First I have created 3 calculated field to get current qtr sales, last 2 qtr sales and 3qtr ago sales as under

      Sales 3QAgo ->

      If qtr = "FY16-Q3" then [Sales] else 0 end

       

      Sales Last2Q ->

      If (qtr = "FY16-Q4" OR Qtr = "FY17-Q1") then [Sales] else 0 end

       

      Sales CQ ->

      If qtr = "FY17-Q2" then [Sales] else 0 end

       

      I created a calculated field (Account category) which uses INCLUDE LOD expression to put account in each category.

      Account category ->

      if (({ INCLUDE [Account Id] :sum([Sales 3QAgo]) > 0}) AND ({ INCLUDE [Account Id] :sum([Sales Last2Q]) = 0}) AND ({ INCLUDE [Account Id] :sum([Sales CQ]) < 0})) then "Group1"

      elseif (({ INCLUDE [Account Id] :sum([Sales 3QAgo]) > 0}) AND ({ INCLUDE [Account Id] :sum([Sales Last2Q]) = 0}) AND ({ INCLUDE [Account Id] :sum([Sales CQ]) > 0})) then "Group2"

      else "Others"

      end

       

      But using INCLUDE make the calculated fields as a measure.

      Since this is a measure, so we are unable to count the number of accounts in different category represented by "Account Category" calculated field. 

       

      Please suggest us how can we do this.