Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have date field and Eventid field .
exampledata :
EventId date
10 01/jun/2016
10 01/july/2016
10 01/aug/2017
10 01/sep/2018
11 01/oct/2016
11 01/nov/2016
11 01/aug/2017
11 01/dec/2018
12 01/feb/2016
12 01/jan/2016
13 01/jan/2017
14 01/mar/2017
15 01/feb/2017
15 01/feb/2018
I need to count the eventId last 12 months.I am using below expression.but it is showing no data.
count({<maxdate1={'>=$(=MonthsStart(Max(maxdate1),-11))<=$(=date(Max(maxdate1)))'}>}[Event ID]).
my dimension: monthyear
jun-2016
jul-2016
.
.
feb-2018.
please help on this.
Thanks
Hi,
would you be able to share the format of your field: maxdate1 ?
Hi,
Could you try with this:
Count({<maxdate1={">=$(=MonthStart(AddMonths(Max(maxdate1)),-11))<=$(=MonthEnd(Max(maxdate1)))"}>}[Event ID]).
(Given that it is formatted as an actual date field as the guys above are trying to ask about)
Best,
Ali A
try this
sum({<MyDate={">$(=AddMonths(Max(MyDate),-12))"}>}Sales)
or try with your date format
=sum({<Datefield={">=$(=date(addmonths(monthstart(today()),-12),'YYYY-MM-DD'))<=date(monthstart(today()),'YYYY'-MM-DD)"}>}sales)
please try below maxdate1 is different then inlined
=Count({<date={">=$(=MonthStart(AddMonths(Max(date)),-11))<=$(=MonthEnd(Max(date)))"}>}EventId)
Hi thanks to all for your help. I got the solution.
But one thing I need my month dimension is not in the order. How to set up the order.
Ex:
My original month field data :
feb-2017
Oct-2016
Nov-2017
Dec-2018
Aug-2016
Jan-2018
The above data not in order. I need the data like below
Month:
Aug-2016
Oct-2016
Feb-2017
Nov-2017
Jan-2018
Dec-2018
Like that. Please help on this. And it is possible to 4 expression in line chat in qliksense.
Thanks
Hi
But one thing I need my month dimension is not in the order. How to set up the order.
I am using below exp .
count({<[Event Category]={'Near Miss'},maxdate1={">=$(=MonthStart(AddMonths(Max(maxdate1)),-11),'MMM/YYYY')<=(=MonthEnd(Max(maxdate1))),'MMM/YYYY')"}>}DISTINCT [Event ID])
Ex:
My original month field data :
feb-2017
Oct-2016
Nov-2017
Dec-2018
Aug-2016
Jan-2018
The above data not in order. I need the data like below
Month:
Aug-2016
Oct-2016
Feb-2017
Nov-2017
Jan-2018
Dec-2018
Please check this.
Data:
LOAD *,Date(monthstart(date),'MMM-YYYY') as Month;
LOAD EventId,Date#(Capitalize(date),'DD/MMM/YYYY') as date INLINE [
EventId,date
10,01/jun/2016
10,01/july/2016
10,01/aug/2017
10,01/sep/2018
11,01/oct/2016
11,01/nov/2016
11,01/aug/2017
11,01/dec/2018
12,01/feb/2016
12,01/jan/2016
13,01/jan/2017
14,01/mar/2017
15,01/feb/2017
15,01/feb/2018
];