Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I have a table with some fields. I have created a pivot table. In this table I am showing Company,Category,MonthYear and Sum of Sales.
My requirement is that I need to Show only those records where MonthYear for Category Names are same.
Example: For Company Boombastic there are 2 categories:1: Men's cloth,2:Men's Footwear;
So I have to track only those Sales for whom Sales has been happened in common period.
Means I need to capture in chart this result::
Company Name Category MonthYear Sales
Boombastic Men's Cloth 01-2008 1611.2
Boombastic Men's Cloth 05-2008 1285.85
Boombastic Men's footwear 01-2008 2451.9
Boombastic Men's footwear 05-2008 1234.72
Here Periods are same.rest will be ignore.
Similarly same for all the companies.
Only Sales for same period for Categories of a company if happens then we have to capture those records.
Thanks & Regards
Rishikesh
To get the total's you will need to use Aggr() function:
=Sum(Aggr(If(Count(DISTINCT TOTAL <MonthYear, CompanyName> CategoryName) = Count(DISTINCT TOTAL <CompanyName> CategoryName), Sum(Sales)), CompanyName, CategoryName, MonthYear))
May be this expression:
=If(Count(DISTINCT TOTAL <MonthYear> CategoryName) = 2, Sum(Sales))
My bad, try this instead:
=If(Count(DISTINCT TOTAL <MonthYear, CompanyName> CategoryName) = Count(DISTINCT TOTAL <CompanyName> CategoryName), Sum(Sales))
To get the total's you will need to use Aggr() function:
=Sum(Aggr(If(Count(DISTINCT TOTAL <MonthYear, CompanyName> CategoryName) = Count(DISTINCT TOTAL <CompanyName> CategoryName), Sum(Sales)), CompanyName, CategoryName, MonthYear))
Hi Sunny,
My result format is below:
CompanyName | CategoryName | MonthYear | Sales |
Boombastic | Men's Clothes | 01-2008 | 16112.02 |
Boombastic | Men's Clothes | 05-2008 | 1285.85 |
Boombastic | Men's Footwear | 01-2008 | 2451.9 |
Boombastic | Men's Footwear | 05-2008 | 1234.72 |
Boleros | Children's Clothes | 01-2008 | 1761.55 |
Boleros | Children's Clothes | 03-2008 | 1655.27 |
Boleros | Children's Clothes | 04-2008 | 1369.16 |
Boleros | Men's Clothes | 01-2008 | 8717.45 |
Boleros | Men's Clothes | 03-2008 | 25890.57 |
Boleros | Men's Clothes | 04-2008 | 14020.94 |
Boleros | Men's Footwear | 01-2008 | 14384.26 |
Boleros | Men's Footwear | 03-2008 | 9703.12 |
Boleros | Men's Footwear | 04-2008 | 15844.86 |
Bond Ltd will not be Capture because it has only one Category. Minimum 2 categories are also required.
Try this:
=If(Count(DISTINCT TOTAL <MonthYear, CompanyName> CategoryName) = Count(DISTINCT TOTAL <CompanyName> CategoryName) and Count(DISTINCT TOTAL <CompanyName> CategoryName) > 1, Sum(Sales))
Thanks Summy.It is correct.
Rishi -
the name is Sunny and not Summy.
Best,
Sunny