Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

rishikeshtiwari
New Contributor II

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

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

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

7 Replies
MVP
MVP

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

May be this expression:

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

MVP
MVP

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

My bad, try this instead:

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


Capture.PNG

MVP
MVP

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

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
New Contributor II

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

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.

MVP
MVP

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

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
New Contributor II

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

Thanks Summy.It is correct.

MVP
MVP

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

Rishi -

the name is Sunny and not Summy.

Best,

Sunny