problem in rolling 12 months data expression?

[gkcchowdary]

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

[YoussefBelloum]

Hi,

would you be able to share the format of your field: maxdate1 ?

[bwisealiahmad]

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

[Chanty4u]

try this

sum({<MyDate={">$(=AddMonths(Max(MyDate),-12))"}>}Sales)

[Chanty4u]

or try with your date format

=sum({<Datefield={">=$(=date(addmonths(monthstart(today()),-12),'YYYY-MM-DD'))<=date(monthstart(today()),'YYYY'-MM-DD)"}>}sales)

[sunilkumarqv]

please try below maxdate1 is different then inlined

=Count({<date={">=$(=MonthStart(AddMonths(Max(date)),-11))<=$(=MonthEnd(Max(date)))"}>}EventId)

[gkcchowdary]

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

[gkcchowdary]

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

[qlikviewwizard]

Hi chaitanya krishna,

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

];