Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
sunny_talwar

Try may be this:

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

View solution in original post

5 Replies
sunny_talwar

Try may be this:

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

Not applicable
Author

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

You given like MonthStart fn.

sunny_talwar

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

Not applicable
Author

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 ?




sunny_talwar

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