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: 
patelm12
Contributor II
Contributor II

Qlik Sense Pivot Table - show 3 weeks future from today and only 9 weeks past

Hi Everyone,

 

I'm building a "productivity" qliksense table. It looks at how many appointments were scheduled in the past and even provides us with a glimpse at future appointments (I coded it to show appointments scheduled for 3 weeks in the future.)

 

The issue is that the pivot table I'm using (I need this for my stakeholders) is very large. I wanted to make this pivot table only show 13 weeks total: 

  1. Previous 9 weeks
  2. This week
  3. 3 weeks in the future

 

The measure is a "count" of all the appointments. Each column is a week. See screenshot below.

 

How would I go about doing this?

patelm12_0-1604347325988.png

 

Labels (3)
1 Solution

Accepted Solutions
MayilVahanan

Hi @patelm12 

You have Date field, so try like below

=Count({<Date={">=$(=WeekStart(Max(Date),-9))<=$(=WeekEnd(Max(Date),3))"}, Year=, Month=, Week=>}distinct CSN)

 

1. I put this into the Measure portion of the pivot table, right? <-- Yes

2.  In the original equation, you included: "Year=, Month=, Week=," <-- its ignore your year, month & week selections.

Your expression looks wrong. We can't use multiple aggregation function, and set analysis syntax is wrong.

You can find many set analysis pdf file in community. Sample links are below:

https://community.qlik.com/t5/Qlik-Education-Documents/Set-Analysis-Presentation-pdf/ta-p/1487937

https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetA...

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

View solution in original post

6 Replies
MayilVahanan

Hi @patelm12 

Try like this

=Count({<Date={">=$(=WeekStart(Max(Date),-9))<=$(=WeekStart(Max(Date),3))"}, Year=, Month=, Week=>}appointments)

Exclude all Time filters and replace Date field with your date field name.

Hope it helps

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
patelm12
Contributor II
Contributor II
Author

Thank you for helping,  @MayilVahanan !

I'm trying to understand the equation you shared so I can use/create more in the future. 

1. I put this into the Measure portion of the pivot table, right?

2.  In the original equation, you included: "Year=, Month=, Week=,"

Why do we include this?

 

I tried adding values to the year, month, and week at the end and I kept getting an error. I know I messed up, but can you help me understand how?

 

This is what I did to your equation:

=Count({<(weekstart(ENCOUNTER_DATE))={">=$(=WeekStart(Max(weekstart(ENCOUNTER_DATE)),-9))<=$(=WeekStart(Max(weekstart(ENCOUNTER_DATE)),3))"}, year= 2020, month = 11, week = 11/1/2020>} Count(distinct CSN)})

 

 

MayilVahanan

Hi @patelm12 

Can you send the sample file.

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
patelm12
Contributor II
Contributor II
Author

Hi @MayilVahanan 

I've been trying to send you a sample, but I don't think my organization allows me to share externally.

I've included screenshots below - does this help? Thank you!!

patelm12_0-1604433088281.pngpatelm12_1-1604433123447.pngpatelm12_2-1604433154090.png

 

MayilVahanan

Hi @patelm12 

You have Date field, so try like below

=Count({<Date={">=$(=WeekStart(Max(Date),-9))<=$(=WeekEnd(Max(Date),3))"}, Year=, Month=, Week=>}distinct CSN)

 

1. I put this into the Measure portion of the pivot table, right? <-- Yes

2.  In the original equation, you included: "Year=, Month=, Week=," <-- its ignore your year, month & week selections.

Your expression looks wrong. We can't use multiple aggregation function, and set analysis syntax is wrong.

You can find many set analysis pdf file in community. Sample links are below:

https://community.qlik.com/t5/Qlik-Education-Documents/Set-Analysis-Presentation-pdf/ta-p/1487937

https://help.qlik.com/en-US/sense/September2020/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/SetA...

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
patelm12
Contributor II
Contributor II
Author

@MayilVahanan 

 

Thanks for sending this over + the additional link. Someone in my organization directed me to create a master calendar instead, which took me a step in another direction. 

I