MTD

[#1]

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

[sunny_talwar]

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:

Attaching the qvw for reference

[#3]

ALT_DATE	MAR	AACTION	Date	COUNTRY
2015-05-01T00:57:31.61Z	150401SCBLAEADBXXX0724515584	CANCEL	2015-05-01	AU
2015-04-06T01:19:21.15Z	150401SCBLAEADBXXX0724515602	ERRIDE	2015-04-06	UAE
2015-06-02T00:18:29.69Z	150401SCBLAEADBXXX0724515370	HOLD	2015-06-02	UAE
2015-04-01T01:23:20.98Z	150401SCBLAEADBXXX0724515606	OK	2015-04-01	AU
2015-05-01T01:03:21.03Z	150401SCBLAEADBXXX0724515592	OK	2015-05-01	UAE
2015-05-02T01:03:21.03Z	150401SCBLAEADBXXX0724515593	OK	2015-05-02	AU
2015-05-03T01:03:21.03Z	150401SCBLAEADBXXX0724515600	OK	2015-05-03	AU
2015-05-04T01:03:21.03Z	20150401N008818979-1	OK	2015-05-04	UAE
2015-06-01T00:20:34.60Z	150401SCBLAEADBXXX0724515376	OK	2015-06-01	UAE
2015-06-01T00:20:34.60Z	150401SCBLAEADBXXX0724515379	OK	2015-06-01	AU
2015-06-02T00:04:25.76Z	150401SCBLAEADBDIF0724514887	OK	2015-06-02	AU
2015-05-03T01:03:21.03Z	150401SCBLAEADBXXX0724515599	SVET	2015-05-03	UAE

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

[sunny_talwar]

This?

[#5]

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

[sunny_talwar]

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?

[#7]

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

[sunny_talwar]

This?

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)

[#9 · Accepted solution]

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?

[sunny_talwar]

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?