Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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')
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.
I use this as dimension, I want to display -- month.year , not day.month.year
if i understood correctly
in dimension Add Calculated Dimension.. -> Month(_DATEFIELD)&'.'&Year(_DATEFIELD)-> and Supress when valued is null
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')
=Date(MonthName(if(year([Date])=2018, [Date])),'MM.YYYY') -- Thanks! I added if and got the desired result!
Or, simply:
=Date(Date, 'MM.YYYY')
There is no point using one formatting function inside another.
@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
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:
😋