topic Re: How to get minimum date for each month dimension using set analysis in QlikView

How to get minimum date for each month dimension using set analysis

Fri, 07 Nov 2014 12:59:06 GMT

Hello everyone,

I have a table with Month as a dimension. My expression is the following:

only({$<Calendardate={"$(=date(min(Calendardate)))"}>} Calendardate)

It returns the the overall minimum date in the data for the minimum month in the dimension, though I want it to return the minimum date for the month in the dimension.

Note that I'm actually trying something more complex than this and that's why I need to set the condition in set analysis.

Many thanks!!

Sergio Peschiera

Re: How to get minimum date for each month dimension using set analysis

Fri, 07 Nov 2014 16:09:55 GMT

DATE(MIN(AGGR(MIN(CALENDERDATE),MONTHFIELD,YEARFIELD)),'DD/MM/YYYY')

Re: How to get minimum date for each month dimension using set analysis

Fri, 07 Nov 2014 16:22:01 GMT

Hello Pooja,

Thanks for your quick answer! Although, I need the formula to be inside a set analysis condition because I'm also setting other conditions and the date I'm comparing is different from my regular date field. The thing is that, whatever I try, the condition seems to ignore all dimensions and calculate the overall minimum date. I've also tried with the aggregate function, but it still doesn't seem to work.

In other words, what I want is that if I have a month dimension, I want the ={"$(=date(min(Calendardate)))"} to return the minimum date of each month value in the dimension and not just for the lowest month in the dimension (which is currently happening).

Example:

What I want:
January February March April May June.....

01/01/2014 01/02/2014 01/03/2014 01/04/2014 01/05/2014 01/06/2014

What I'm getting:

January February March April June July.....

01/01/2014 - - - - -

Please feel free to ask any me any more details .

Thanks again!

Re: How to get minimum date for each month dimension using set analysis

mohsinqlik — Fri, 31 Jul 2015 16:31:55 GMT

I am facing the same scenario where i am not getting minimum date of each month.

Wanted to know if you found any way to get it.