Filtering Pivot Table Data

[sekharQV]

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

[sekharQV]

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

[#3]

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

[#4]

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

[sekharQV]

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

[#6]

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

[#7]

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

[sekharQV]

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

[#9 · Accepted solution]

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

[sekharQV]

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

[#11]

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?