Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
gkcchowdary
Creator
Creator

problem in rolling 12 months data expression?

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

8 Replies
YoussefBelloum
Champion
Champion

Hi,

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

bwisealiahmad
Partner
Partner

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
Champion III
Champion III

try this

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

Chanty4u
Champion III
Champion III

or try with your date format

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

sunilkumarqv
Specialist II
Specialist II

please try below maxdate1 is different then inlined

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

gkcchowdary
Creator
Creator
Author

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
Creator
Creator
Author

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
Master II
Master II

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

];

Capture.PNG

Capture.PNG