topic Re: MTD in QlikView

MTD

Wed, 10 Jun 2015 12:23:05 GMT

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

Re: MTD

sunny_talwar — Wed, 10 Jun 2015 12:32:02 GMT

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

Re: MTD

Wed, 10 Jun 2015 12:51:46 GMT

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

Re: MTD

sunny_talwar — Wed, 10 Jun 2015 12:59:13 GMT

This?

Re: MTD

Wed, 10 Jun 2015 13:10:39 GMT

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

sunny_talwar — Wed, 10 Jun 2015 13:13:31 GMT

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?

Re: MTD

Wed, 10 Jun 2015 13:16:19 GMT

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

Re: MTD

sunny_talwar — Wed, 10 Jun 2015 13:22:23 GMT

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)

Re: MTD

Wed, 10 Jun 2015 13:40:03 GMT

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

sunny_talwar — Wed, 10 Jun 2015 13:55:35 GMT

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?

Re: MTD

Wed, 10 Jun 2015 14:02:08 GMT

Thanks Mate. It was very helpful

Is it possible to Divide the last month MTD Volume / Previous month MTD Volume

In the example We need to divide June2 days volume by May 2days volume for each country

Re: MTD

sunny_talwar — Wed, 10 Jun 2015 14:04:10 GMT

No problem

You should be able to do that... let me see if I can make than work for you.

Re: MTD

Wed, 10 Jun 2015 14:22:42 GMT

We are able to find it using your expression. Thanks

Re: MTD

sunny_talwar — Wed, 10 Jun 2015 14:24:00 GMT

So it worked now?

Re: MTD

Wed, 10 Jun 2015 14:25:23 GMT

In the above example , if i want to show the countries only with morethan 100 % increase compare to last month.

How do i display that countries alone

Thanks

Re: MTD

Wed, 10 Jun 2015 14:25:40 GMT

Yes.. Perfectly

Re: MTD

Fri, 12 Jun 2015 11:17:00 GMT

Below is my Table Viewer Snapshot.

When i link ALT1 in AMSR_Apr and ALT_DATE in sheet 1 i get a circular Reference.

How do i avoid that

Circular Reference

Regards

Senthil