Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
mrthomasshelby
Contributor III

Prevent a Filter from affecting a chart

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!


stalwar1loveisfail



1 Solution

Accepted Solutions

Re: Prevent a Filter from affecting a chart

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])

    )

View solution in original post

9 Replies
sibusiso90
Contributor II

Re: Prevent a Filter from affecting a chart

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))

  ))

sibusiso90
Contributor II

Re: Prevent a Filter from affecting a chart

Hi Phalgun

The above will ignore any selections that are done.

mrthomasshelby
Contributor III

Re: Prevent a Filter from affecting a chart

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

sibusiso90
Contributor II

Re: Prevent a Filter from affecting a chart

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

mrthomasshelby
Contributor III

Re: Prevent a Filter from affecting a chart

Okay thanks for the quick response!

Re: Prevent a Filter from affecting a chart

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

mrthomasshelby
Contributor III

Re: Prevent a Filter from affecting a chart

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

Re: Prevent a Filter from affecting a chart

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])

    )

View solution in original post

mrthomasshelby
Contributor III

Re: Prevent a Filter from affecting a chart

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