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,

As per our org security policies, i can not share my data model.

And the GLDate logic implemented by is not working for contract tab.

Please suggest me.

Thanks,

Rajasekhar

[#3]

Could you update the qvw I attached earlier or create a new qvw with inline tables to simulate the issue?

I'm really struggling to understand the issue,

- Stan

[sekharQV]

Hi Bennet,

Please find Attached QVW that you have shared earlier.

I tried INLINE load of 'GLFilter' using this file. It works partially in contract tab. And for other tabs i don't know how to implement the same  functionality.Because there is no GLDate column in all other tabs.

So i tried in other way by creating multi box by using below expression directly as dimension value.

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

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

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

)))

The above expression created a multi box with only 2 values '2 Weeks, 1 Month'. and '2 Months' is not displayed in multi box.

And my table is responding for '1 Month' value. For '2 Weeks' , it is not responding (Value is in Grey shade).

Could you please suggest me any corrections in above expression?

And which is better option for displaying GLFilter  values? Either INLINE Load or directly writing expression in multi box?

Regards,

Rajasekhar

!

[#5]

Is there any way you can simulate the issue in a qvw? The one you attached is working fine.

Regarding the formula that is not working I believe it should be as follows:

=if(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')))>=31 and today()-makedate(year(date#(GLDate,'YYYYMMDD')),month(date#(GLDate,'YYYYMMDD')),day(date#(GLDate,'YYYYMMDD')))<61,'>1 Month',if(today()-makedate(year(date#(GLDate,'YYYYMMDD')),month(date#(GLDate,'YYYYMMDD')),day(date#(GLDate,'YYYYMMDD')))>14,'>2 Weeks')))

However, is that really what you want? The formula is saying if a date is greater than 2 months, then it is NOT greater than 1 month. Also, if a date is greater than 1 month then it is NOT greater than 2 weeks. So, for example; the GL Date of 4/19/2016 will display when you choose > 2 Months. This is good. When you choose > 1 Month, it will NOT display. It will also not display for > 2 Weeks. I wouldn't think this is what you want, but I could be wrong.

Also, with this formula you will not see each of the 3 options (> 2 Weeks, > 1 Month and > 2 Months) unless there are GLdates that fall into those buckets. For example; you currently don't have any GLDates falling into the > 2 Weeks bucket and so that option is not displaying in the Multibox. Tomorrow (Thur) it will show because the GLDate of 8/31/2016 will then be more than 14 days ago.

Attached to this reply is a qvw with the updated formula.

So, regarding is Inline or expression better really depends on the requirement. With Inline you will always have the 3 options in the Multibox. With the expression you will not.

Hope this helps!

- Stan