topic Re: Qlik Sense Pivot Table - show 3 weeks future from today and only 9 weeks past in App Development

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

patelm12 — Sat, 16 Nov 2024 01:11:05 GMT

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:

Previous 9 weeks
This week
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?

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

MayilVahanan — Tue, 03 Nov 2020 03:06:55 GMT

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

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

patelm12 — Tue, 03 Nov 2020 14:06:24 GMT

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

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

MayilVahanan — Tue, 03 Nov 2020 16:02:11 GMT

Hi @patelm12

Can you send the sample file.

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

patelm12 — Tue, 03 Nov 2020 19:52:42 GMT

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

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

MayilVahanan — Wed, 04 Nov 2020 00:59:40 GMT

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/SetAnalysis/set-analysis-expressions.htm

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

patelm12 — Mon, 09 Nov 2020 16:26:54 GMT

@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.