Skip to main content
Announcements
Accelerate Your Success: Fuel your data and AI journey with the right services, delivered by our experts. Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
jalanhart
Contributor III
Contributor III

Filter Dimension when using Alternate States

I am trying to create sales info for a customizable "Current Period" and "Prior Period" setup similar to what is in our company's ERP.

I used the same field INVOICE_DATE.autocalendar.YearMonth and created two filters with alternate states for each period.

While that does work on the surface, it feels really limiting because I cannot seem to filter down any dimension field. I am using a pivot table format, and I cannot seem to filter down to one specific customer or product family or sales rep or anything. 

Is there a way around this, or is this just inherent to Alt States in QLIK?

 

THX! 

Labels (1)
5 Replies
Lisa_P
Employee
Employee

It depends on how you use Alternate states and what your objective is. Are you applying it to the whole table or a measure ?

jalanhart
Contributor III
Contributor III
Author

Hey Lisa,

I have a pivot table that has dimensions as the rows for customer and sales part, and measures as values one for QTY sold and one for sales in the current period and one for the prior period. 

I have only applied to measures within the table iutself, such as --  sum({[Current Period]} INVOICED_QTY)

When filtering on the date filters with the alternate states applied they work great, but the table isn't as useful if it's not filterable. In this table below I would need to be able to filter on any dimension and it still shows the sales according to the time period chosen.

jalanhart_0-1714490579689.png

 

Lisa_P
Employee
Employee

You can also include selections from the default state using 

sum({[Current Period]*$} INVOICED_QTY) to show the overlap of the state and selections

jalanhart
Contributor III
Contributor III
Author

thanks again, and while that seems to turn all other customers' data to zero if I filter on one specific customer, it doesn't remove them from the table at all, which is what I need it to do. 

Basically what I'm trying to say is if I have this little table. I want to be able to select a Current Period (CP) and a Prior Period (PP) using a date picker or just a filter pane field, and then be able to filter on just CUSTOMER B or whatever, and have the pivot table only show customer B. 

CUSTOMER  QTY CP SALES CP QTY PP SALES PP
A 1 10 1 8
B 2 20 2 16
C 3 30 3 24
D 4 40 4 32
jalanhart
Contributor III
Contributor III
Author

Hi Lisa, for some reason I edited my reply and then it got deleted somehow, or at least I don't see it.

That expression did not work the way I wanted it to because while it seemed to only show the values for the customer I filtered on, it didn't filter the whole table down to just the filtered customer like it does if I am not using alternate states, which is what I want it to do. 

If this is my sheet, I want to select my alt state time periods using a date picker or just a YEAR-MONTH filter pane and then have the pivot table narrow down to just customer C in this example is I wanted to 

jalanhart_0-1714641007446.png