topic Re: Filtering Pivot Table Data in QlikView

Filtering Pivot Table Data

PRS — Tue, 06 Sep 2016 14:24:11 GMT

Hi,

I have below urgent requirements, Please help me.

Requirement1

TRevenue	TMargin	TMargin%
32,393	17529	54%
23,145	12,258	47%
22,735	8,175	36%
Some Data	Some Data	37%
Some Data	Some Data	36%
Some Data	Some Data	54%
Some Data	Some Data	100%
Some Data	Some Data	45%

Here i want to add two filters using multiboxes TMargin%< and TMargin%>

-->Drop down values in T Margin % < filters should be 5, 10, 15, 20, 25.

-->Drop down values in T Margin % > filters should be 75, 80, 85, 90, 95.

-->Also there should be an option to view all values in the report. That is not applying any of the above two filters

Requirement2:

I have a table like below

Contarct No	GLDate
111	20160831
112	20160808
113	20160804
114	20160808
115	20160809
116	20160605
117	20160409

I want to adda a filter using multibox on ‘GLDate’. The drop down values should be > 2Weeks, > 1 Month and > 2 Months. GL Date should be compared with today’s date to identify whether it is > 2 weeks or > 1 month etc.

Can anybody help me regarding these requirements.

Thanks,

Rajasekhar

Re: Filtering Pivot Table Data

agomes1971 — Tue, 06 Sep 2016 17:00:01 GMT

Hi,

please see attached for requirement 1

HTH

André Gomes

Re: Filtering Pivot Table Data

agomes1971 — Tue, 06 Sep 2016 17:15:51 GMT

Hi,

please see attached for requirement 2

HTH

André Gomes

Re: Filtering Pivot Table Data

Tue, 06 Sep 2016 18:04:19 GMT

For Requirement 1, please see the attached Multiboxes.qvw

The key pieces are as follows:

1. Two inline tables for the valid % selections (i.e. 5%, 10%, etc...)

LowMargins:
load * inline [
Low Margin%
0
5
10
15
20
25
];

HighMargins:
load * inline [
High Margin%
75
80
85
90
95
100
];

2. Two variables to capture the Multibox selections and to handle if nothing selected:

vSelectedLowMargin = If(isnull(GetFieldSelections([Low Margin%])),0,GetFieldSelections([Low Margin%]))

vSelectedHighMargin = If(isnull(GetFieldSelections([High Margin%])),100,GetFieldSelections([High Margin%]))

In the Chart or Table enter the following expression in place of the Margin% field:

=(Aggr(Margin,Customer,Product,Month) >= vSelectedLowMargin and Aggr(Margin,Customer,Product,Month) <= vSelectedHighMargin) * -1 * Margin

In the expression above the two Aggr functions inside the parentheses will either return a 0 for false or a -1 for true. If the result is false then 0 * Margin will be zero and not included in the chart. If the result is true then Margin will be multiplied * -1 so the Margin will be included in the chart. The extra -1 is so the Margin% is not negative.

For Requirement 2, you should be able to use the same technique to get what you need.

Hope this helps!

- Stan

Re: Filtering Pivot Table Data

PRS — Wed, 07 Sep 2016 05:40:53 GMT

Hi,

Thanks for reply. But when i am trying to add 5% also in TMargin%< list box, it is not showing. Can you halp regarding this?

Thanyou,

Rajasekhar

Re: Filtering Pivot Table Data

agomes1971 — Wed, 07 Sep 2016 08:37:39 GMT

Hi,

please see attached.

HTH

André Gomes

Re: Filtering Pivot Table Data

PRS — Wed, 07 Sep 2016 15:15:52 GMT

Hi Andre,

Thank you very much for your reply.

Actually i have Inline table table like below.

LOAD * INLINE [

GLFilter

> 2 Weeks

> 1 Month

> 2 Months

];

So i want to create a filter on GLDate using above field(GLFilter) in multibox.

Could please guide me?

Thanks,

Rajasekhar

Re: Filtering Pivot Table Data

PRS — Wed, 07 Sep 2016 16:43:47 GMT

Hi Andre,

Thank you very much for your reply.

Actually i have Inline table table like below.

LOAD * INLINE [

GLFilter

> 2 Weeks

> 1 Month

> 2 Months

];

So i want to create a filter on GLDate using above field(GLFilter) in multibox.

Could please guide me?

Thanks,

Rajasekhar

Re: Filtering Pivot Table Data

agomes1971 — Wed, 07 Sep 2016 17:12:54 GMT

Hi,

then you have to change your data model.

Your way will not work if you don't change the data Model.

Regards

André Gomes

Re: Filtering Pivot Table Data

Wed, 07 Sep 2016 22:26:54 GMT

Hi,

I believe the attached qvw will give you what you are asking for, but please let me know!

Thanks!

- Stan

Re: Filtering Pivot Table Data

PRS — Thu, 08 Sep 2016 11:35:33 GMT

Hi Bennet,

Thanks for reply.

I followed your steps and i am able to create filters on TMargin%< and TMargin%>.

But i am unable show all values when clicking 'SeeAllValues' on button.

Because in my case TMargin% is not a Field. It is an expression like TMargin%= TMargin/TRevenue.

I followed below steps.

1.I created two inline tables to take values of TMargin % <(0.05,0.01,0.15,0.2) and TMargin % >(0.75,0.80,0.85,,0.90)

2.variable vT1=[TMargin % <]

variable vT2=[TMargin % >]

3.And i created two Multiboxes(for filtering purpose) using above two variables

4.expression for TMargin%= if( ([T Margin]/[T Revenue]) < $(vT1) or ([T Margin]/[T Revenue]) > $(vT2) , ([T Margin]/[T Revenue]))

So, filters are working fine.

But, when i click on 'SeeAllValues' Button, It is showing all values in report except For TMargin% column.

In TMargin% column, it is showing empty data ('-').

So, how can show all the values in the report.

I can not share my QVW file . It is restricted in my org.

Thanks, Rajasekhar

Re: Filtering Pivot Table Data

Thu, 08 Sep 2016 16:53:50 GMT

I will check out your code, but in the meanwhile, can you send me the code for the button?

Re: Filtering Pivot Table Data

Thu, 08 Sep 2016 17:17:50 GMT

Try changing your vT1 formula to:

variable vT1 = If(isnull(GetFieldSelections([TMargin % <])),1,GetFieldSelections([TMargin % <]))

Then change your vT2 formula to:

variable vT2 = If(isnull(GetFieldSelections([TMargin % >])),0,GetFieldSelections([TMargin % >]))

Does this fix the 'SeeAllValues' button?

- Stan

Re: Filtering Pivot Table Data

PRS — Thu, 08 Sep 2016 17:18:53 GMT

The Actual Expression TMargin%= TMargin/TRevenue.

For filtering purpose i changed it as

TMargin%= if( ([T Margin]/[T Revenue]) < $(vT1) or ([T Margin]/[T Revenue]) > $(vT2) , ([T Margin]/[T Revenue])).

And i tried vT1=0 and vT2=0 conditions on 'SeeAllValues' button. But this functionality didn't work.

So, i am also trying to implement that same functionality to show all report values irrespective filters selections

Re: Filtering Pivot Table Data

Thu, 08 Sep 2016 22:00:03 GMT

Please send all of the variable definitions and all of the table expression definitions that you had when the vT1=0 and vT2=1 conditions did not work.

Also, what actions is the SeeAllValues button doing? Is it clearing fields or is it setting some variable values?

Then I will be of better help to you,

- Stan

Re: Filtering Pivot Table Data

Fri, 09 Sep 2016 02:12:52 GMT

Hi,

Please see the 2 qvw files that are attached. The one named MultiboxesReq1.qvw is my attempt at duplicating your issue.

The one named MultiboxesReq1_Fix.qvw has the issue fixed.

The only difference between the 2 qvw files is the formula for vT1 and vT2 are different.

Please check them out and let me know what you think.

Hopefully this is what you need,

- Stan

Re: Filtering Pivot Table Data

PRS — Fri, 09 Sep 2016 06:07:41 GMT

Hi Bennet,

Thank you very much for your efforts. It's worked for me.

But again my requirement has been changed.

Now i made TMargin and TRevenue also as expressions like below

TMargin=(Sum({<[CATEGORYCODE]={'TR'}>}[AMOUNT])*(-1))-Sum({<[CATEGORYCODE]={'TM','TL','TO'}>}[AMOUNT])

TRevenue=(Sum({<[CATEGORYCODE]={'TR'}>}[AMOUNT])*(-1))

TMargin%= if( ([T Margin]/[T Revenue]) < $(vT1) or ([T Margin]/[T Revenue]) > $(vT2) , ([T Margin]/[T Revenue])).

That means here TMargin, TRevenue and TMargin% all are expressions.

When TMargin and TRevenue are fields, your solution was worked.

But now i removed these two fields and and i created them as expressions like above.

In this case, your solution is not working for me.

so, how can i chieve this.

Regards,

Rajasekhar

Re: Filtering Pivot Table Data

Fri, 09 Sep 2016 20:23:44 GMT

I had to do some tweaking of the expression in the chart table, but looks like it is working fine.

Please check out the attached qvw and let me know if this is correct.

- Stan

Re: Filtering Pivot Table Data

PRS — Tue, 13 Sep 2016 17:40:57 GMT

Hi Bennet,

Thanks for guiding me.

Actually i tried to implement Date filter in my App. But it is not working fine.

I have different tabs like Region,Route,Supervisor,SalesRep,Customer,Contract. And every tab has associated pivot table.

In Contract tab only i have 'GLDate' column. So that i can apply filter(GLFilter) by writing expression on 'GLDate' field.

Now, i want to apply the same filter(GLFilter) in all the other Tabs also. But here the problem is, there is no 'GLDate' field in all these tabs. And i can not add that 'GLDate' column to pivot table against user requirement.

So that i can not apply Filter directly.

For this i tried some expression through multibox as suggested by agomes1971like below.

=if(today()-makedate(year(date#(GLDate,'YYYYMMDD')),month(date#(GLDate,'YYYYMMDD')),day(date#(GLDate,'YYYYMMDD')))<11,'2 weeks',

if(today()-makedate(year(date#(GLDate,'YYYYMMDD')),month(date#(GLDate,'YYYYMMDD')),day(date#(GLDate,'YYYYMMDD')))>=11 and today()-makedate(year(date#(GLDate,'YYYYMMDD')),month(date#(GLDate,'YYYYMMDD')),day(date#(GLDate,'YYYYMMDD')))<31,'1 Month',

if(today()-makedate(year(date#(GLDate,'YYYYMMDD')),month(date#(GLDate,'YYYYMMDD')),day(date#(GLDate,'YYYYMMDD')))>=31 and today()-makedate(year(date#(GLDate,'YYYYMMDD')),month(date#(GLDate,'YYYYMMDD')),day(date#(GLDate,'YYYYMMDD')))<61,'2 Months',

if(today()-makedate(year(date#(GLDate,'YYYYMMDD')),month(date#(GLDate,'YYYYMMDD')),day(date#(GLDate,'YYYYMMDD')))>=61,'>2 Months'))))

But it is not giving output for some filter values(1month,>2months).

I want to add a a filter using multibox on ‘GLDate’. The drop down values should be > 2Weeks, > 1 Month and > 2 Months. GL Date should be compared with today’s date to identify whether it is > 2 weeks or > 1 month etc.

Initially i added this filter through Inline table.

Can you suggest on how to achieve this?

Thanks,

Rajasekhar

Re: Filtering Pivot Table Data

Wed, 14 Sep 2016 01:28:38 GMT

Can you send a picture of your data model?

Also, is the logic in the qvw I sent for the GLDate working for the Contract tab?