Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rsingh43
Contributor

Count ID with Max(OutDate)

Hi All,

Hope someone is able to help me with this issue.

I have a table containing the below data:

IDPartNoPartNoOutReceivedDateOutDate
3037680-132-0039000002370152018-04-10 16:08:00.0002018-05-31 16:12:42.567
3037680-132-0039000002461102018-04-10 16:08:00.0002018-08-28 13:17:09.173
3037680-132-0039000002502462018-04-10 16:08:00.0002018-05-31 16:12:42.567

I need to create a table in Qlik counting numbers of units out but using the MAX(OutDate). So the table in Qlik will show as the following:

Month/YearUnits Out
Oct-20181

As the ID is the same I only need to count this as one unit out.

Thanks in advance.

Tags (2)
3 Replies
OmarBenSalem
Esteemed Contributor

Re: Count ID with Max(OutDate)

First of all,

in the script create ur monthYear field as follow

Capitilize(Date(MonthStart(OutDate),'MMM-YYYY')) as MonthYear


Now in ur table, use

as dimension: MonthYear

as Measure: Count(distinct {<OutDate={"$(=max(total <ID> OutDate))"}>} PartNoOut)


Hope this helps !

rsingh43
Contributor

Re: Count ID with Max(OutDate)

Hi Omar,

I tried to add Capitilize(Date(MonthStart(OutDate),'MMM-YYYY')) as MonthYear into the script but this does not run due to the 'Capitilize()' section not being accepted.

Ignore it was just a spelling error. I have added everything as suggested but still get a result of 3.

OmarBenSalem
Esteemed Contributor

Re: Count ID with Max(OutDate)

It capitalize; my bad lol

Community Browser