topic Re: Auto Filter on Table???? in QlikView

Auto Filter on Table????

Thu, 21 Mar 2013 10:50:41 GMT

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

Re: Auto Filter on Table????

sushil353 — Thu, 21 Mar 2013 10:57:59 GMT

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

Re: Auto Filter on Table????

Thu, 21 Mar 2013 11:05:55 GMT

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!

Re: Auto Filter on Table????

Thu, 21 Mar 2013 11:33:35 GMT

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

Re: Auto Filter on Table????

sushil353 — Thu, 21 Mar 2013 11:56:55 GMT

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)

Re: Auto Filter on Table????

Thu, 21 Mar 2013 12:00:16 GMT

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

Re: Auto Filter on Table????

jagan — Thu, 21 Mar 2013 12:01:55 GMT

Hi,

Please find attached file for solution.

Regards,

Jagan.