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

[agomes1971]

Hi,

please see attached for requirement 1

HTH

André Gomes

[agomes1971]

Hi,

please see attached for requirement 2

HTH

André Gomes

[#4]

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

[sekharQV]

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

[agomes1971]

Hi,

please see attached.

HTH

André Gomes

[sekharQV]

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

[sekharQV]

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

[agomes1971]

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

[#10]

Hi,

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

Thanks!

- Stan