Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

MTD

Hi

ALT_DATE

MAR

AACTION

2015-06-02T00:04:25.76Z

150401SCBLAEADBDIF0724514887

OK

2015-06-02T00:18:29.69Z

150401SCBLAEADBXXX0724515370

HOLD

2015-06-01T00:20:34.60Z

150401SCBLAEADBXXX0724515376

OK

2015-06-01T00:20:34.60Z

150401SCBLAEADBXXX0724515379

OK

2015-05-01T00:57:31.61Z

150401SCBLAEADBXXX0724515584

CANCEL

2015-05-01T01:03:21.03Z

150401SCBLAEADBXXX0724515592

OK

2015-05-02T01:03:21.03Z

150401SCBLAEADBXXX0724515593

OK

2015-05-03T01:03:21.03Z

150401SCBLAEADBXXX0724515599

SVET

2015-05-04T01:03:21.03Z

20150401N008818979-1

OK

2015-05-03T01:03:21.03Z

150401SCBLAEADBXXX0724515600

OK

2015-04-06T01:19:21.15Z

150401SCBLAEADBXXX0724515602

ERRIDE

2015-04-01T01:23:20.98Z

150401SCBLAEADBXXX0724515606

OK

In the above sample data , We  need to find the MTD count of DISTINCT MAR with AACTION = OK , CANCEL, HOLD  for last 2 Months

1 Solution

Accepted Solutions
Not applicable

Re: MTD

Hi

LOAD ALT_DATE,

    MAR,

    AACTION,

    Date#(Date, 'YYYY-MM-DD') as Date,

    COUNTRY

FROM

[https://community.qlik.com/thread/167773]

(html, codepage is 1252, embedded labels, table is @2);

Which Date you are mentioning in this? What is the use of it?

16 Replies

Re: MTD

Try this expression:

=Count(DISTINCT {<AACTION = {'OK'}, Date = {"$(='>=' & Date(AddMonths(MonthStart(Max(Date)),-1), 'YYYY-MM-DD') & '<=' & Date(MonthEnd(Max(Date)), 'YYYY-MM-DD'))"}>} MAR)

Here Date field is calculated in the script:

Table:

LOAD ALT_DATE,

    MAR,

    AACTION,

    Date#(Left(ALT_DATE, 10), 'YYYY-MM-DD') as Date

FROM

[https://community.qlik.com/thread/167773]

(html, codepage is 1252, embedded labels, table is @1);


Output:

Capture.PNG


Attaching the qvw for reference

Not applicable

Re: MTD

ALT_DATEMARAACTIONDateCOUNTRY
2015-05-01T00:57:31.61Z150401SCBLAEADBXXX0724515584CANCEL2015-05-01AU
2015-04-06T01:19:21.15Z150401SCBLAEADBXXX0724515602ERRIDE2015-04-06UAE
2015-06-02T00:18:29.69Z150401SCBLAEADBXXX0724515370HOLD2015-06-02UAE
2015-04-01T01:23:20.98Z150401SCBLAEADBXXX0724515606OK2015-04-01AU
2015-05-01T01:03:21.03Z150401SCBLAEADBXXX0724515592OK2015-05-01UAE
2015-05-02T01:03:21.03Z150401SCBLAEADBXXX0724515593OK2015-05-02AU
2015-05-03T01:03:21.03Z150401SCBLAEADBXXX0724515600OK2015-05-03AU
2015-05-04T01:03:21.03Z20150401N008818979-1OK2015-05-04UAE
2015-06-01T00:20:34.60Z150401SCBLAEADBXXX0724515376OK2015-06-01UAE
2015-06-01T00:20:34.60Z150401SCBLAEADBXXX0724515379OK2015-06-01AU
2015-06-02T00:04:25.76Z150401SCBLAEADBDIF0724514887OK2015-06-02AU
2015-05-03T01:03:21.03Z150401SCBLAEADBXXX0724515599SVET2015-05-03UAE

Country wise Last 2 Months MTD Distinct MAR Count for AACTION = OK

In the data , we have got data for June only till 2nd , Hence when you take the count for MAY it should consider only till 2nd MAY

Re: MTD

This?

Capture.PNG

Not applicable

Re: MTD

HI,

If you look at the data for AU for MAy we have got only one MAr till 2nd May .

You should not be taking MAy 3rd data as June data is available onl;y till 2nd Jun

Re: MTD

Oh so you want from May 1st till May 2nd and From June 1st to June 2nd (excluding the 2nd in both cases?) the distinct count of MAR?

Not applicable

Re: MTD

Including 2nd . Since June has got only 2 days data , May and April should take only till 2nd.

Re: MTD

This?

Capture.PNG

Expression:

=Count(DISTINCT {<AACTION = {'OK'}, Date = {"$(='>=' & Date(MonthStart(Max(Date)), 'YYYY-MM-DD') & '<=' & Date(Max(Date), 'YYYY-MM-DD'))"} + {"$(='>=' & Date(AddMonths(MonthStart(Max(Date)), -1), 'YYYY-MM-DD') & '<=' & Date(AddMonths(Max(Date), -1), 'YYYY-MM-DD'))"}>} MAR)

Not applicable

Re: MTD

Hi

LOAD ALT_DATE,

    MAR,

    AACTION,

    Date#(Date, 'YYYY-MM-DD') as Date,

    COUNTRY

FROM

[https://community.qlik.com/thread/167773]

(html, codepage is 1252, embedded labels, table is @2);

Which Date you are mentioning in this? What is the use of it?

Re: MTD

I wasn't sure if QlikView will read the date above as date (Most likely it would have, but I wanted to be safe) and so I used Dat#() function to force QlikView to read it as a Date.

Does that make sense?

Community Browser