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 values of Date in MMM-YYYY format

Hi ,

I need to show date dimension in format "Jun-2010" , "Jul-2010" in Bar Chart , to achieve this I have used Date(Claim_Date,'MMM-YYYY') and I can see values in same format in chart but these values are appearing duplicate . example Jun-2010,Jun-2010, Jul-2010,Aug-2010,Aug-2010, Aug-2010


I want these values to be appear unique as Jun-2010 , Jul-2010, Aug-2010, I have used DISTINCT LOAD as mentioned below inside script but its not working


Claim:

LOAD

Claim_ID,

Claim_Date,

Claim_Amount,

.....

.....

......

FROM

CLAIM_TABLE ;


Temp:

LOAD

DISTINCT Date(Claim_Date,'MMM-YYYY') As ClaimDateMMMYYYY

RESIDENT Claim;


1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

The Date() function only formats the textual portion of the Dual Date field to the desired format, while the numeric values remain unique for each day.

In order to generate distinct monthly numbers, apply the MonthStart() function to your date:

Date(MonthStart(Claim_Date),'MMM-YYYY') As ClaimDateMMMYYYY

We usually call this field MonthYear, to be easily recognizable.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business.

View solution in original post

3 Replies
Clever_Anjos
Employee
Employee

LOAD

DISTINCT Date(monthstart(Claim_Date),'MMM-YYYY') As ClaimDateMMMYYYY

RESIDENT Claim;

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

The Date() function only formats the textual portion of the Dual Date field to the desired format, while the numeric values remain unique for each day.

In order to generate distinct monthly numbers, apply the MonthStart() function to your date:

Date(MonthStart(Claim_Date),'MMM-YYYY') As ClaimDateMMMYYYY

We usually call this field MonthYear, to be easily recognizable.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business.

TheMdu
Contributor III
Contributor III

Thanks for this. Wanted to display unique records  (Datevalues) in the multibox and I found this to be helpful.