Announcements
cancel
Showing results for
Did you mean:
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?

Labels (3)

• SaaS

1 Solution

Accepted Solutions
MVP

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

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

MVP

Can you send the sample file.

Thanks & Regards, Mayil Vahanan R
Contributor II
Author

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

MVP

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