topic Re: Issue on dates in QlikView

Issue on dates

Wed, 25 Nov 2020 16:16:04 GMT

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 — Fri, 11 Nov 2016 10:56:21 GMT

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"}>}

Fri, 11 Nov 2016 11:25:46 GMT

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 — Fri, 11 Nov 2016 12:50:57 GMT

May be try this:

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

rubenmarin — Fri, 11 Nov 2016 13:04:42 GMT

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