topic Re: Prevent a Filter from affecting a chart in App Development

Prevent a Filter from affecting a chart

mrthomasshelby — Wed, 24 May 2017 07:29:54 GMT

Hello all! I have a bar chart with calculated measure as:

Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'}>} Aggr(

If( Rangesum(Above(Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'} >} [Sales])/Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'} >} total [Sales]),1,RowNo()))<=0.8,

Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'} >}[Sales])

)

([Vendor],(=Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'} >}[Sales]),Desc))

))

Now I also have 3 filters which are calculated as follows:

1.Bucket by PO:

Aggr(If(Sum({1}{<[Type]={'N'}>}[Sales]) < 5000, Dual('5k or Below',1),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 50000, Dual('5k-50k',2),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 100000, Dual('50k-100k',3),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 250000, Dual('100k-250k',4),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 500000, Dual('250k-500k',5),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 1000000, Dual('500k-1Mn',6),

If(Sum({1}{<[[Type]={'N'}>}[Sales]) < 10000000, Dual('1Mn-10Mn',7),

Dual('>10Mn',8)))))))), [PO#])

2.Bucket by Vendor:

Aggr(If(Sum({1}{<[Type]={'N'}>}[Sales]) < 5000, Dual('5k or Below',1),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 50000, Dual('5k-50k',2),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 100000, Dual('50k-100k',3),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 250000, Dual('100k-250k',4),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 500000, Dual('250k-500k',5),

If(Sum({1}{<[Type]={'N'}>}[Sales]) < 1000000, Dual('500k-1Mn',6),

If(Sum({1}{<[[Type]={'N'}>}[Sales]) < 10000000, Dual('1Mn-10Mn',7),

Dual('>10Mn',8)))))))), [Vendor])

3.Vendor pareto:

Aggr(

If(Rangesum(Above(Sum({<[Vendor]>} [Sales])/Sum({<[Vendor]>} TOTAL [Sales]),0,RowNo()))<=0.8, Dual('Top 80%', 1),

If(Rangesum(Above(Sum({<[Vendor]>} [Sales])/Sum({<[Vendor]>} total [Sales]),0,RowNo()))<=0.95, Dual('80-95%', 2),

Dual('Bottom 5%', 3))),

([Vendor],(=Sum({<[Vendor]>} [Sales]),Desc))

)

Now I don't want these 3 filters to affect my bar chart. If any selections are made on these filters, the bar chart should not change. Is there any way to achieve this? TIA!

stalwar1‌ loveisfail‌

Re: Prevent a Filter from affecting a chart

sibusiso90 — Wed, 24 May 2017 07:32:13 GMT

Sum({1<[Sales]={"<=$(=$(vInput))"},[Type]={'N'}>} Aggr(

If( Rangesum(Above(Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'} >} [Sales])/Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'} >} total [Sales]),1,RowNo()))<=0.8,

Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'} >}[Sales])

)

([Vendor],(=Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'} >}[Sales]),Desc))

))

Re: Prevent a Filter from affecting a chart

sibusiso90 — Wed, 24 May 2017 07:33:55 GMT

Hi Phalgun

The above will ignore any selections that are done.

Re: Prevent a Filter from affecting a chart

mrthomasshelby — Wed, 24 May 2017 07:34:12 GMT

Thanks but this will stop all filters from affecting the chart. I have other filters that need to work on this bar chart.

Re: Prevent a Filter from affecting a chart

sibusiso90 — Wed, 24 May 2017 07:38:11 GMT

From my understanding, That is not possible because of the association.

Re: Prevent a Filter from affecting a chart

mrthomasshelby — Wed, 24 May 2017 07:39:22 GMT

Okay thanks for the quick response!

Re: Prevent a Filter from affecting a chart

sunny_talwar — Wed, 24 May 2017 08:33:47 GMT

May be try ignoring selection in PO#, Vendor and sales because those are the underlying fields behind your calculated filters.

Re: Prevent a Filter from affecting a chart

mrthomasshelby — Wed, 24 May 2017 08:36:00 GMT

How can I do that? Could you please elaborate a little?

Re: Prevent a Filter from affecting a chart

sunny_talwar — Wed, 24 May 2017 08:38:40 GMT

Hahaha basic set analysis

Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'}, Vendor, PO#, Sales>} Aggr(

If( Rangesum(Above(Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'}, Vendor, PO#, Sales>} [Sales])/Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'}, Vendor, PO#, Sales>} total [Sales]),1,RowNo()))<=0.8,

Sum({<[Sales]={"<=$(=$(vInput))"},[Type]={'N'}, Vendor, PO#, Sales>}[Sales])

)

Re: Prevent a Filter from affecting a chart

mrthomasshelby — Wed, 24 May 2017 08:44:05 GMT

Wow! This works like magic! Thanks a lot again!