Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP & Luminary
MVP & Luminary

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.

Oleg Troyansky

Masters Summit for Qlik is going virtual! Dec 16th - I'll teach Performance Tuning

View solution in original post

3 Replies
Highlighted
Employee
Employee

LOAD

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

RESIDENT Claim;

Highlighted
MVP & Luminary
MVP & Luminary

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.

Oleg Troyansky

Masters Summit for Qlik is going virtual! Dec 16th - I'll teach Performance Tuning

View solution in original post

Highlighted
Contributor III
Contributor III

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