Set Analysis with variables

[matancha]

Hello all.

I'm want to show sales in the same period (of selection) of 12 months back

.

I hold 2 variables:

vDateMin = MonthStart(AddMonths(min(Date), -12))

vDateMax = MonthEnd(AddMonths(Max(Date), -12))

And now I want to use the variable in pivot table expression like: Sum({$<Date = {"> $(#vDateMin) < $(#vDateMax)"}>}$(vSales))

and It doesn't work for me.

if I do it manually like this: Sum({$<Date = {">=01/01/2011 <= 28/02/2012"}>}$(vSales))  Its work fine.

What am I doing wrong?

Tnks -

Matan.

[fdelacal]

Hi plzmatanch

Sum({$<Date = {"$(='>'&(vDateMin)&'<'(vDateMax)"}>}$(vSales))

please trie this way.

hope that helps you.

[jagan · Accepted solution]

Hi,

Try this expression

vDateMin = MonthStart(AddMonths(Max(Date), -12))

vDateMax = MonthEnd(AddMonths(Max(Date), -12))

=Sum({<YearDimensionName=, MonthDimensionName=, QuarterDimensionName=, Date = {">=$(=vDateMin)<=$(=vDateMax)"}>}$(vSales))

Exclude the date filters like Year, Month or Quarter selections if there are any.  Also check whether the date formats are same in Variable and Dimension.

Hope this helps you.

Regards,

Jagan.

[its_anandrjs]

Hi,

Please check your date format of the variable  Date  .

Regards,

Anand

[#5]

your script working fine.

try this

=Sum({$<Date = {'> $(=vDateMin) <$(=vDateMax)'}>}$(vSales))

[matancha]

Tnks jagan.

I Think that my problem was that I didn't do the "Year=...."

many thanks to all of you for your answers -

Matan.

[vinod22kv]

Hi,

can i have some simple example on this please.

Regards,

Vinod