16 Replies Latest reply: Jun 12, 2015 7:17 AM by Senthil Kumar

# MTD

Hi

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

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:

MAR,

AACTION,

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

FROM

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

Output:

Attaching the qvw for reference

• ###### Re: MTD

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?

• ###### 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?

• ###### Re: MTD

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

• ###### Re: MTD

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

Hi

MAR,

AACTION,

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

COUNTRY

FROM

(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?

• ###### Re: MTD

Hi

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

No problem

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

• ###### Re: MTD

Hi

We are able to find it using your expression. Thanks

• ###### Re: MTD

So it worked now?

• ###### Re: MTD

Yes.. Perfectly

• ###### Re: MTD

Hi

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

Regards

Senthil

• ###### Re: MTD

Hi

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