topic Re: problem in rolling 12 months data expression? in QlikView

problem in rolling 12 months data expression?

gkcchowdary — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: problem in rolling 12 months data expression?

YoussefBelloum — Wed, 01 Aug 2018 09:57:19 GMT

Hi,

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

Re: problem in rolling 12 months data expression?

bwisealiahmad — Wed, 01 Aug 2018 09:58:34 GMT

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

Re: problem in rolling 12 months data expression?

Chanty4u — Wed, 01 Aug 2018 10:01:49 GMT

try this

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

Re: problem in rolling 12 months data expression?

Chanty4u — Wed, 01 Aug 2018 10:02:39 GMT

or try with your date format

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

Re: problem in rolling 12 months data expression?

sunilkumarqv — Wed, 01 Aug 2018 10:05:58 GMT

please try below maxdate1 is different then inlined

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

Re: problem in rolling 12 months data expression?

gkcchowdary — Thu, 02 Aug 2018 05:22:50 GMT

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

Re: problem in rolling 12 months data expression?

gkcchowdary — Fri, 03 Aug 2018 06:39:55 GMT

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

Re: problem in rolling 12 months data expression?

qlikviewwizard — Fri, 03 Aug 2018 07:12:26 GMT

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

];