Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Distinct

Hi

How can i get distinct values in below function

Now i am getting duplicate values.

May-2016

May-2016

------

Date(ACT_START_DATE, 'MMM-YY')

Thanks

Govind R

1 Solution

Accepted Solutions
MVP
MVP

Re: Distinct

Try may be this:

Date(MonthStart(ACT_START_DATE), 'MMM-YY') as ACT_START_MONTHYEAR

5 Replies
MVP
MVP

Re: Distinct

Try may be this:

Date(MonthStart(ACT_START_DATE), 'MMM-YY') as ACT_START_MONTHYEAR

Not applicable

Re: Distinct

Yes your correct how it possible.Same way mee to given.Am i correct.

You given like MonthStart fn.

MVP
MVP

Re: Distinct

I don't understand your follow up question, can you explain please

Not applicable

Re: Distinct

When i given like

Date(ACT_START_DATE, 'MMM-YY') it comming with duplicate records.


But when i give like Date(MonthStart(ACT_START_DATE), 'MMM-YY') it coming without duplicate.



How it possible.MonthStart will remove duplicate ?




MVP
MVP

Re: Distinct

Date is just a formatting function and doesn't remove the day from your date just because it is now formatted as MMM-YY. For example

Capture.PNG

They all look Aug-16, but they are all have different underlying values which you might be able to see if you use Num() function around it.

Now when you use MonthStart, it first finds the Month start value for each of the date and then assigns in the new formatting

Capture.PNG

Community Browser