Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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)
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
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!
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
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)
Sushil, you are a living genius thank you so much!!!!
Hi,
Please find attached file for solution.
Regards,
Jagan.