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))))

[#2]

Miguel,

I took the variables out of my script and declared them in the variable overview. The reason for doing this was because I didnt want the date to be static. For instance if I checked May-2010 I wanted the varibles to reflect this date.

For example:

Date_MonthYear is in my table "DateParts" at the beginning of my script.

"Date as Date

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

vDateNow = Date_MonthYear

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

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

I have three textboxes on my sheet. The values they display are as follows:

• Textbox1 - vDateNow: which gives me the value of the current selected month/year. For instance "May 2010"
• Textbox2 - vDate12: which gives the value "May 2009"
• Textbox3 - vDate24: which gives the value "May 2008"

In my pivot chart I have a number of expression, for example the following, which gives me the correct value.

count({$< Date_MonthYear = {">=$(vDate12)<=$(vDateNow)"} >} if(analysis_a='11', val*(36.75/100), (val-(despatched_qty*cost_of_sale))))

however the current expressions are giving a '0' value.

count({$< Date_MonthYear = {">=$(vDate24)<=$(vDate12)"} >} if(analysis_a='11', val*(36.75/100), (val-(despatched_qty*cost_of_sale))))

Is there a reason why the last expression will not work, even though the dates are being displayed with no problem on the textboxes?

Apologies for leaving such a long post, I hope I explained it clearly.

[Miguel_Angel_Baeyens · Accepted solution]

Brian,

According to your variable definitions, in all cases the result is a string. Strings can use the operators "follow" and "precede" is you want to make some text comparison, but this is not the case. So you need some additional transformation in either your expression or your script to get that right, something like

Count({$< Date_MonthYear = {"=Date(Date#(Date_MonthYear, 'MMM YYYY')) >= Date(Date#($(vDate24), 'MMM YYYY'))", "=Date(Date#(Date_MonthYear, 'MMM YYYY')) <= Date(Date#($(vDate12), 'MMM YYYY'))"} >} if(analysis_a='11', val*(36.75/100), (val-(despatched_qty*cost_of_sale))))

So now you are comparing a number to a number, which is fine. That seems too messy though, doesn't it? I'd rather play with the AddMonths function and the Date field (the complete DD/MM/YYYY field), so why not use instead

Count({$< Date = {">=$(=AddYears(vDateNow, -2))<=$(=AddYears(vDateNow, -1))"} >} if(analysis_a='11', val*(36.75/100), (val-(despatched_qty*cost_of_sale))))

And set vDateNow as a variable having a full date but represented as 'MMM YYYY', or even better, a Slider/Calendar object (calendar) where you select the month by clicking on any day of that month.

Hope that makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#4]

Thanks for the help Miguel!