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

I have a data in the following format…

 Account Id Product Qtr sales A X FY17-Q2 200 B X Fy16-Q4 250 B X FY17-Q1 30 A Y FY16-Q3 40 C Z FY16-Q3 100

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.

• ###### 1. Re: How to count different account category created using INCLUDE LOD expression

Hey Shivnath,

This question would be more likely to receive a response with a packaged workbook with sample data. If you have sensitive information I would check out Anonymize your Tableau Package Data for Sharing.

Thanks,

-Diego