Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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
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
This?
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
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?
Including 2nd . Since June has got only 2 days data , May and April should take only till 2nd.
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)
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?
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?