1 2 Previous Next 17 Replies Latest reply on Mar 17, 2016 7:06 PM by swaroop.gantela

Applying a filter to only specific parameter value

Hi All,

I am trying to apply filter for just a particular parameter value.

Example,

I have 5 parameter values:

1) Members Acquired

2) Loyalty

3) Sales

4) Expired Members

5) Live Members

I want to apply the filter just to "Expired Members" but not able to do so.

Here is the screenshot of my workbook.

The filter "Expired members" has the formula:

IF [ExpiryDate] >= today() THEN

"valid"

ELSE

"Expired"

End

( [ExpiryDate] is a field)

and row column in the screenshot where you can see  "AGG(Sales metric to view frequncy)" has the formula

IF [Metric to View] = 'Members Acquired' then SUM([Total Members])

ELSEIF [Metric to View] = 'Acquisition Rate' then [AQRate]

ELSEIF [Metric to View] = 'Live Member Group' then SUM([Total Members])

ELSEIF [Metric to View] = 'Loyalty Sales' then SUM([Nett])

ELSEIF [Metric to View] = 'Member Frequency' then [FRE_TRND_AllMembers]

ELSEIF [Metric to View] = 'Member Ticket Average' then [TA_Members]

ELSEIF [Metric to View] = 'Member Points' then SUM([Points])

ELSEIF [Metric to View] = 'Expired Members' then SUM([Total Members])

end

How can I filter "Expired Members" parameter without affecting other parameter values?

Kind Regards,

Shivang

• 1. Re: Applying a filter to only specific parameter value

Hi Shivang,

I guess there is no problem with the "SUM([Total Members])" measure because it's working fine with 'Live Member Group' selection.

It seems your case statement shouldn't matched with parameter value, "Edit parameter" and  check lower and upper case letters on "Expired Members" and also check for whitespaces, hope this should help.

1 of 1 people found this helpful
• 2. Re: Applying a filter to only specific parameter value

Hi Shivang Desai

Parameter Value as text should match with the case statements. what Akram pointed out..

3 of 3 people found this helpful
• 3. Re: Applying a filter to only specific parameter value

It seems that the question has been misunderstood.

[there are 2 things : filter (Expired member) and paramter value ( Expired Members) see the screenshot in filter section.

As you said, the parameter value is correct. If you see the screenshot there is a filter called "Expired Members".

I want this filter to be applied only when parameter value "Expired Members" is selected

And for other parameter values this filter should not be applied.

I hope you got my query.

Sent from my iPhone

• 4. Re: Applying a filter to only specific parameter value

It seems that the question has been misunderstood.

[there are 2 things : filter (Expired member) and paramter value ( Expired Members) see the screenshot in filter section.

As you said, the parameter value is correct. If you see the screenshot there is a filter called "Expired Members".

I want this filter to be applied only when parameter value "Expired Members" is selected

And for other parameter values this filter should not be applied.

I hope you got my query.

• 5. Re: Applying a filter to only specific parameter value

Hi Ragu Nath,

That is not what I am asking for. My question is different.

Thanks

• 6. Re: Applying a filter to only specific parameter value

Hi,

First clarify this.

Expired Members is a dimension or not?

What is split_new?

If  Its Measure:

I selected Expired Members in the parameter other values should not affected means?

My understanding is,

You want Expired Members measure should be be visible when u need to compare with others.. if yes use two parameters.

or add that measure to column.It will always visible.

If its Dimension

We cant apply filter for one particular measure alone. Need to change little bit in the data set..

1 of 1 people found this helpful
• 7. Re: Applying a filter to only specific parameter value

Shivang,

Would it work to add extra conditional into

IF [Metric to View] = 'Members Acquired' then SUM([Total Members])

ELSEIF [Metric to View] = 'Expired Members' AND [Expired Members]="Valid" then SUM([Total Members])

...

Please see if the attached matches what you are looking for, or please modify to match.

• 8. Re: Applying a filter to only specific parameter value

THAT GIVES AN ERROR "CANNOT MIX AGGREGATE AND NON AGGREGATE RESULTS OR EXPRESSIONS IN IF"

• 9. Re: Applying a filter to only specific parameter value

Shivang,

Do you necessarily need to SUM within your conditional?

IF [Metric to View] = 'Members Acquired' then [Total Members]

ELSEIF [Metric to View] = 'Expired Members' AND [Expired Members]="Valid" then [Total Members]

and then sum the Metric? Please see the attached in previous message.

Or are some of your metrics aggregated in different ways or some are not aggregated?

• 10. Re: Applying a filter to only specific parameter value

Shivang,

I think you can make your conditional without any aggregations like:

IF [Metric to View] = 'Members Acquired' then [Total Members]

ELSEIF [Metric to View] = 'Expired Members' AND [Expired Members]="Valid" then [Total Members]

and the make a separate calculation that will aggregate each type as needed:

AggregateMetric:

IF [Parameter 1]="Sales" THEN SUM([Metric])

ELSEIF [Parameter 1]="Profits" THEN AVG([Metric])

ELSEIF [Parameter 1]="ExpiredMembers" THEN ATTR([Metric])

END

• 11. Re: Applying a filter to only specific parameter value

Hi Swapnil,

To be precise I need to use SUM.

• 12. Re: Applying a filter to only specific parameter value

Hi Swapnil,

The below will throw an error in calculation saying "Cannot mix aggregate and non-aggregate ....."

IF [Metric to View] = 'Members Acquired' then [Total Members]

ELSEIF [Metric to View] = 'Expired Members' AND [Expired Members]="Valid" then [Total Members]

......

• 13. Re: Applying a filter to only specific parameter value

IF [Metric to View] = 'Members Acquired' then [Total Members]

ELSEIF [Metric to View] = 'Acquisition Rate' then [AQRate]

ELSEIF [Metric to View] = 'Live Member Group' then [Total Members]

ELSEIF [Metric to View] = 'Loyalty Sales' then [Nett]

ELSEIF [Metric to View] = 'Member Frequency' then [FRE_TRND_AllMembers]

ELSEIF [Metric to View] = 'Member Ticket Average' then [TA_Members]

ELSEIF [Metric to View] = 'Member Points' then [Points]

ELSEIF [Metric to View] = 'Expired Members' AND [Expired members] = "Expired" then [Total Members]

end

The above throes an error because [FRE_TRND_AllMembers] and a few of them are calculated fields.

• 14. Re: Applying a filter to only specific parameter value

But when I use the below:

IF [Metric to View] = 'Members Acquired' then [Total Members]

ELSEIF [Metric to View] = 'Expired Members' AND [Expired members] = "Expired" then [Total Members]

end

It is valid. Don't know why.

1 2 Previous Next