Ggroup by month use Set Analysis

[Vittore8]

Hi, please tell me how I can group by month I use set analysis? And display

01.2017
01.2018
02.2017
02.2018

=if(year([Date]) = 2018, [Date]) -- so far I write like this, but it is not correct

[eliran]

Hi,

If you need it in the expression, and you don't use month/year fields or a calendar, the easiest way to do is like this:

sum(if(Year(Date)=2018,<Field>))

You can use more advanced ways with set analysis, but the above is the basic.

Regards,

Eliran.

[Vittore8]

I use this as dimension, I want to display -- month.year , not day.month.year

[Taoufiq_Zarra]

if i understood correctly

in dimension Add Calculated Dimension.. -> Month(_DATEFIELD)&'.'&Year(_DATEFIELD)-> and Supress when valued is null

[Vegar · Accepted solution]

I don't think that SET analysis will solve your issue, but you can obtain what you want by using a calculated dimension like this:

=Date(MonthName(Date),'MM.YYYY')

Screenshot from QlikView, but it will behave similarly in Qlik Sense

[Vittore8]

=Date(MonthName(if(year([Date])=2018, [Date])),'MM.YYYY') -- Thanks! I added if and got the desired result!

[tresesco]

Or, simply:

=Date(Date, 'MM.YYYY')

There is no point using one formatting function inside another.

[Vegar]

@tresesco  There is a quite crucial differene between MonthName() and Date(). MonthName() is not only a formatting function as Date() is. MonthName is also of a transform/convertion function.

MonthName() converts the date (or timestamp) into the first day of the month and both the dual text and numeric value will change.

Date(field, 'MM.YYYY') formats the dual text into the desired format (here MM.YYYY), but the underlying numeric value will not change. So when only using =Date(Date,'MM.YYYY') you will get multiple unique fieldvalues with identical text representation. See my attached image below.

In my earlier example I used the outer date() just to format the presentation as the post author wanted. I could just have used monthname(Date), but then the presentation would have been  the default MMM YYYY.

BR
Vegar

[tresesco]

Hi @Vegar ,

I always had this problem of not remembering which are formatting and which are transforming (value changing) functions in qlik date, time function list. Before posting I quickly checked the help and read:

and just didn't complete reading it:

 

😋