Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
rishikeshtiwari
Creator
Creator

In Pivot table capture the Sales for only Same period of time

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

1 Solution

Accepted Solutions
sunny_talwar

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))

View solution in original post

7 Replies
sunny_talwar

May be this expression:

=If(Count(DISTINCT TOTAL <MonthYear> CategoryName) = 2, Sum(Sales))

sunny_talwar

My bad, try this instead:

=If(Count(DISTINCT TOTAL <MonthYear, CompanyName> CategoryName) = Count(DISTINCT TOTAL <CompanyName> CategoryName), Sum(Sales))


Capture.PNG

sunny_talwar

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))

rishikeshtiwari
Creator
Creator
Author

Hi Sunny,

My result format is below:

   

CompanyNameCategoryNameMonthYearSales
BoombasticMen's Clothes01-200816112.02
BoombasticMen's Clothes05-20081285.85
BoombasticMen's Footwear01-20082451.9
BoombasticMen's Footwear05-20081234.72
BolerosChildren's Clothes01-20081761.55
BolerosChildren's Clothes03-20081655.27
BolerosChildren's Clothes04-20081369.16
BolerosMen's Clothes01-20088717.45
BolerosMen's Clothes03-200825890.57
BolerosMen's Clothes04-200814020.94
BolerosMen's Footwear01-200814384.26
BolerosMen's Footwear03-20089703.12
BolerosMen's Footwear04-200815844.86

Bond Ltd will not be Capture because it has  only one Category. Minimum 2 categories are also required.

sunny_talwar

Try this:

=If(Count(DISTINCT TOTAL <MonthYear, CompanyName> CategoryName) = Count(DISTINCT TOTAL <CompanyName> CategoryName) and Count(DISTINCT TOTAL <CompanyName> CategoryName) > 1, Sum(Sales))


Capture.PNG

rishikeshtiwari
Creator
Creator
Author

Thanks Summy.It is correct.

sunny_talwar

Rishi -

the name is Sunny and not Summy.

Best,

Sunny