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: 
Not applicable

Auto Filter on Table????

Hi Guys and Gals,

I'll try and keep this as simple as I possibly can, otherwise minds will be blown! I have a chart straight table with two columns, Company Name, and Sum of Trend. The Sum of Trend is worked out through rather a complex expression and I really don't want to get into the nitty gritty of it, however all you need to know for now is that it will display a 0, 1, or 2 based on data in other columns, (not mentioned at this stage for simplicity). Now I want to add a slider  with 0,1,2 and I want it to filter the table based on what is selected. How do I 'attach' the slider to that expression in that column?

Kind Regards,

Miles

1 Solution

Accepted Solutions
sushil353
Master II
Master II

in your slider expression:

use this:

=Aggr(if(vDCVYearDiff=1,

(if((sum({$<[SaleYear]={'$(vPriorYear-1)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity]))>(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity])),1,0)+

(if((sum({$<[SaleYear]={'$(vPriorYear-1)'},Prod_Type={'Dispenser'}>} [Salequantity]))<(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Dispenser'}>} [Salequantity])),1,0))),

if(vDCVYearDiff=2,

(if((sum({$<[SaleYear]={'$(vPriorYear-2)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity]))>(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity])),1,0)+

(if((sum({$<[SaleYear]={'$(vPriorYear-2)'},Prod_Type={'Dispenser'}>} [Salequantity]))<(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Dispenser'}>} [Salequantity])),1,0))),

if(vDCVYearDiff=3,

(if((sum({$<[SaleYear]={'$(vPriorYear-3)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity]))>(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity])),1,0)+

(if((sum({$<[SaleYear]={'$(vPriorYear-3)'},Prod_Type={'Dispenser'}>} [Salequantity]))<(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Dispenser'}>} [Salequantity])),1,0))),

if(vDCVYearDiff=4,

(if((sum({$<[SaleYear]={'$(vPriorYear-4)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity]))>(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity])),1,0)+

(if((sum({$<[SaleYear]={'$(vPriorYear-4)'},Prod_Type={'Dispenser'}>} [Salequantity]))<(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Dispenser'}>} [Salequantity])),1,0))),

if(vDCVYearDiff=5,

(if((sum({$<[SaleYear]={'$(vPriorYear-5)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity]))>(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity])),1,0)+

(if((sum({$<[SaleYear]={'$(vPriorYear-5)'},Prod_Type={'Dispenser'}>} [Salequantity]))<(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Dispenser'}>} [Salequantity])),1,0)))))))),GroupCustomerName)

View solution in original post

6 Replies
sushil353
Master II
Master II

you can add expression into the slider...in the field dropdown you can select expression..

if that doesn't work.. then please post the sample application

Not applicable
Author

hmmm, nope that didn't work, nice try though...I will work on a blank dashboard as there is a bit of sensitive information in here, gimme 15 minutes!

Not applicable
Author

have attached a demo to the topic at the top, any help much appreciated. It's the Jaw status slider that should control the Sum of Trend column,

Kind Regards,

Miles

sushil353
Master II
Master II

in your slider expression:

use this:

=Aggr(if(vDCVYearDiff=1,

(if((sum({$<[SaleYear]={'$(vPriorYear-1)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity]))>(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity])),1,0)+

(if((sum({$<[SaleYear]={'$(vPriorYear-1)'},Prod_Type={'Dispenser'}>} [Salequantity]))<(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Dispenser'}>} [Salequantity])),1,0))),

if(vDCVYearDiff=2,

(if((sum({$<[SaleYear]={'$(vPriorYear-2)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity]))>(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity])),1,0)+

(if((sum({$<[SaleYear]={'$(vPriorYear-2)'},Prod_Type={'Dispenser'}>} [Salequantity]))<(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Dispenser'}>} [Salequantity])),1,0))),

if(vDCVYearDiff=3,

(if((sum({$<[SaleYear]={'$(vPriorYear-3)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity]))>(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity])),1,0)+

(if((sum({$<[SaleYear]={'$(vPriorYear-3)'},Prod_Type={'Dispenser'}>} [Salequantity]))<(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Dispenser'}>} [Salequantity])),1,0))),

if(vDCVYearDiff=4,

(if((sum({$<[SaleYear]={'$(vPriorYear-4)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity]))>(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity])),1,0)+

(if((sum({$<[SaleYear]={'$(vPriorYear-4)'},Prod_Type={'Dispenser'}>} [Salequantity]))<(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Dispenser'}>} [Salequantity])),1,0))),

if(vDCVYearDiff=5,

(if((sum({$<[SaleYear]={'$(vPriorYear-5)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity]))>(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Cartridge'}>} Prod_NoInCase*[Salequantity])),1,0)+

(if((sum({$<[SaleYear]={'$(vPriorYear-5)'},Prod_Type={'Dispenser'}>} [Salequantity]))<(sum({$<[SaleYear]={'$(vPriorYear)'},Prod_Type={'Dispenser'}>} [Salequantity])),1,0)))))))),GroupCustomerName)

Not applicable
Author

Sushil, you are a living genius thank you so much!!!!

jagan
Luminary Alumni
Luminary Alumni

Hi,

Please find attached file for solution.

Regards,

Jagan.