Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
jeckstein
Partner
Partner

Pivot table expression ignores a pivot table dimension

I have a pivot table that looks like the following.

Sales manger and Sales Rep are the dimensions

Sales Dollars= Sum(Sales)

Days in Period=count(DISTINCT {<MonthID={"<=$(=(Max(MonthID)))"}>} ReportingDate)


Daily Sales=Sum(Sales)/(count(DISTINCT {<MonthID={"<=$(=(Max(MonthID)))"}>} ReportingDate) )

The problem I have here is with my calculation of Days in Period. The number of days in this period is 40, but because of the associative data model Steve smith only made sales on 34 of the 40 days so his days in period is only 34. I need the days in period column to be the same for each Sales Rep. (With the ability to make selections on dates which would change the number of days in period)

Can anyone help????

Sales ManagerSales RepSales DollarsDays in PeriodDaily Sales
John DoeSteve Smith100343
Derek Jeter200297
Tom Brady3003010
Jane SmithMike Trout400667
Lebron James5002520
Larry Bird6004015
Steve Nash7004018
David Ortiz8003523
1 Solution

Accepted Solutions
jeckstein
Partner
Partner
Author

This is what ended up working best for me.

=count(
DISTINCT {<MonthID={"<=$(=(Max(MonthID)))"},ReportingFacilityID=,AgeGroup=,FinancialClassID=,Account=,Zip=>} ReportingDate)


I added ever possible dimension that was able to be included in the pivot table and on the sheet the pivot table was on

which was the following (ReportingFacilityID=,AgeGroup=,FinancialClassID=,Account=,Zip=)


BUT omitted all date fields, which gave me the proper functionality.



View solution in original post

6 Replies
sunny_talwar

May be this

Count(TOTAL <[Sales Manager]> DISTINCT {<MonthID={"<=$(=(Max(MonthID)))"}>} ReportingDate)

jeckstein
Partner
Partner
Author

Sunny this is what I was looking for but to expand this problem a bit further. This pivot table is set up to let the user pick from a list of dimensions what id going to be displayed. When I change the dimensions to be

Sales Manager and MonthYear this logic does not work rather then days in timeframe being the the number of days in the month it shows number of days in entire year regardless of month.

Any ideas?

sunny_talwar

May be you will need to use Aggr() function to make this work

Sum({<MonthID={"<=$(=(Max(MonthID)))"}>}Aggr(Count(TOTAL <[Sales Manager]> DISTINCT {<MonthID={"<=$(=(Max(MonthID)))"}>} ReportingDate), ....))


I am not sure what all dimensions will be needed here, but you can try it out or share a sample to look at

alextimofeyev
Partner
Partner

Sunny,

you were supposed to be on a break

sunny_talwar

This is me on a break...

jeckstein
Partner
Partner
Author

This is what ended up working best for me.

=count(
DISTINCT {<MonthID={"<=$(=(Max(MonthID)))"},ReportingFacilityID=,AgeGroup=,FinancialClassID=,Account=,Zip=>} ReportingDate)


I added ever possible dimension that was able to be included in the pivot table and on the sheet the pivot table was on

which was the following (ReportingFacilityID=,AgeGroup=,FinancialClassID=,Account=,Zip=)


BUT omitted all date fields, which gave me the proper functionality.