Changing expression to Set Analysis

[#1]

Hello,

I have an expression:

count(distinct(if(FinYearLSDate = [Display FinYear] and [Fin Month No] <= [Display Fin Month No], Vehicle_KEY)))

which I would like to change to a set analysis for reasons of excluding the current selection but I'm not sure of the syntax to follow for this particular set analysis.

Please could someone assist on the correct syntax.

Many thanks,

S

[#2]

Hi,

try this:

count( {<FinYearLSDate={[Display FinYear]},[Fin Month No]={'<=$(=[Display Fin Month No])'}>} distinct Vehicle_KEY])

Regards

[#3]

Hi,

No, it's not working. Thanks though. I'll try altering it to see if it comes right.

Shane

[#4]

I have gotten quite close to the correct syntax. Why I say close is because it's calculating the sales for the financial year but is disregarding the <= portion of the set analysis so it gives the complete sum for the financial year.

count({1<FinYearLSDate = {'$(=[Display Fin Year])'} , "[Fin Month No]" = {'$(<=[Display Fin Month No])'}>} distinct Vehicle_KEY)

I get a constant sales amount for every month in the financial year. This figure only changes when I change to a different financial year.

[#5]

Hi,

try removing de " of the [Fin Month No] field and write the <= outside the dollar sign expansion so the expression is:

count({1<FinYearLSDate={'$(=[Display Fin Year])'},[Fin Month No]={'<=$(=[Display Fin Month No])'}>} distinct Vehicle_KEY)

hope this helps

[#6]

Nope, still no luck but I'll carry on trying.

[johnw]

So are Display Fin Year and Display Fin Month No part of a date island? They are not connected to your data, so you're using the if() to connect them in the chart? And you're using the Display Fin year and Display Fin Month No as your dimensions?

If so, then set analysis "can't" do what you want it to do. A set is evaluated once per chart instead of once per row of the chart. There is a complicated way around this, but that's probably not your answer.

A simple way to handle this is to do nothing but add a {1} to your expression:

count({1} distinct if(FinYearLSDate = [Display FinYear] and [Fin Month No] <= [Display Fin Month No], Vehicle_KEY))

It's perfectly legal to combine set analysis with an if() like this. The {1} won't fix the potential performance issues with putting an if() inside of an aggregation, but I'm guessing you don't have a performance problem in practice?

[#8]

John,

Below is the table layout. The problem I'm having is that I'm happy with all the objects in my document except the one I'm posting about. It's the only one where Financial Year is taken into account.

Thanks for the mixing 'if' and 'set analysis' tip. I didn't realize that.

Shane