Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikView forum consolidation is complete. Labels are now required. LEARN ABOUT LABELS
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
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.

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
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.

View solution in original post

TheMdu
Contributor III
Contributor III

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