Issue on dates

[#1]

Hi,

my requirement is to have the selected date range months to appear as  dimensions;

selected date range is the field:PeriodDateFrom and PeriodDateTo.

since i failed to have it as a dimension, trying it as an expression, and tried the below

=Aggr(Only({$<(ValidToDate={">=$(=MonthStart(max(PeriodDateFrom)))<=$(=MonthEnd(max(PeriodDateTo)))"})>}ValidToDate),Monthname(ValidToDate))

but i get an error message.

could someone sort the issue for me?

[sunny_talwar]

You have probably two options here:

1) Use calculated dimension

If(ValidToDate >= PeriodDateFrom and ValidToDate <= PeriodDateTo, ValidToDate)

and then check 'Suppress When Value Is Null' option on the dimension tab

2) Or use set analysis to your existing expressions

{<ValidToDate = {"=ValidToDate >= PeriodDateFrom and ValidToDate <= PeriodDateTo"}>}

[#3]

Hello Sunny,

Thank you for your response, but :

I am going by the first suggestion: calculated dimension( as this will work in my bar chart as well)

this is the calculated dimension i have given ( as without the min n max, the chart showed out of memory)

=monthname(If(ValidToDate >= min(PeriodDateFrom) and ValidToDate <= max(PeriodDateTo), ValidToDate))

SHOWS error in calculation; could you please correct?

[sunny_talwar]

May be try this:

If(ValidToDate >= Min(TOTAL PeriodDateFrom) and ValidToDate <= Max(TOTAL PeriodDateTo), MonthName(ValidToDate))

[rubenmarin]

Hi Mythili, Aggr() expects a dimension name as parameter, using a formula may return unexpected results, maybe:

=Aggr(Monthname(Only({$<(ValidToDate={">=$(=MonthStart(max(PeriodDateFrom)))<=$(=MonthEnd(max(PeriodDateTo)))"})>}ValidToDate)),ValidToDate)

* Not tested, for this to work ValidToDate should be a real date, not a string