Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
The measure is a "count" of all the appointments. Each column is a week. See screenshot below.
How would I go about doing this?
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
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
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)})
Hi @patelm12
Can you send the sample file.
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!!
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
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