Set analysis

[#1]

can anyone see what is wrong with this expression? Is it an issue with syntax? Or is it the fact that I cant embed set analysis into an if statement? Thanks

sum(if(analysis_a='11', {$<Date_MonthYear={">=$(#v3Mth) <=$(#v1Mth)"}>} val*(36.75/100), {$<Date_MonthYear={">=$(#v3Mth) <=$(#v1Mth)"}>} (val-(despatched_qty*cost_of_sale))))

[SunilChauhan]

sum(if(analysis_a='11', {$<Date_MonthYear={">=$(=#v3Mth) <=$(=#v1Mth)"}>} val*(36.75/100), {$<Date_MonthYear={">=$(=#v3Mth) <=$(=#v1Mth)"}>} (val-(despatched_qty*cost_of_sale))))

[Miguel_Angel_Baeyens]

Hi Brian,

It's a combination of both. You can use set analysis in an aggregation function such as Sum(), and use it in conjunction with an If() conditional, with the proper syntax. Besides, make sure that Date_MonthYear has a numeric value, because is what you are forcing when you interpret $(#v3Mth) (the "#" means the result must be interpreted as numeric). So it should look like this

sum({$< Date_MonthYear = {">=$(#v3Mth)<=$(#v1Mth)"} >} if(analysis_a='11', val*(36.75/100), (val-(despatched_qty*cost_of_sale))))

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#4]

Another question please:

I put v3mth =date(addmonths(Date_MonthYear,-3),'MMM-YYYY') in my Variable Overview. This worked correctly.

However when i put it in my script, it did not work:

let v3mth = date(addmonths(Date_MonthYear,-12),'MMM-YYYY');

Any ideas why this happened? Thanks

[#5]

Is there something wrong with this expression?

=date(addmonths(Date_MonthYear,-24),'MMM-YYYY')

[Miguel_Angel_Baeyens]

Brian,

What's the content of the field Date_MonthYear in each case? AddMonths() need a full date field (year, month, day) to work, and "-12" should be accepted as it is -3 or any other valid numeric value, hence my guess that the issue may be in the format of the values in Date_MonthYear.

In any case, values returned by that Date() are not numeric, so the expression above using $(#v3Mth) is unlikely to work. Do they work?

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[Miguel_Angel_Baeyens]

No, there's not.

Assuming Date_MonthYear has the value 23/08/2011 (format DD/MM/YYYY) your expression above should return "Aug-2009".

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#8]

Thanks for the reply Miguel,

Date_MonthYear contains a correct date field.

The code does not work when I put it in the script, but it works correctly when I declare the variables in the Variable Overview.

Here is the code in the script:

Date as invoice_date,

date(monthstart(Date),'MMM-YYYY') as Date_MonthYear,

let vDate12 = date(addmonths(Date_MonthYear,-12),'MMM-YYYY');

let vDate24 = date(addmonths(Date_MonthYear,-24),'MMM-YYYY');

let vDateNow = Date_MonthYear;

[Miguel_Angel_Baeyens]

Brian,

In the script you need to pass on to the Date() functions only one value of all the possible in the field Date_MonthYear. According to your syntax, you are passing the field name, which will not work. Check the attached sample application on how to get that working in the load time.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#10]

Thank you Miguel, I'll try that out