Unique dates in DD-MMM format in x-axis

[PM_QlikDev]

Hi All,

I have a date field with values like 01/01/2016 till 31/12/2020. I want to show a line graph with current month and prev year same month comparison in line graph with x-axis having date format DD-MMM.

What I want is only single unique DD-MMM date for all respective years.

Currently I have extracted the fields 01-Jan,02-Jan,03-Jan and so on irrespective of their years but the values still shows the duplicate values for these. Like 01-Jan(for 2016),01-Jan(for 2017) and for others as well.

for eg if I hover over 01-Jan , then the measure for current month and last yr same month should show.Alternatively, if i select 01-Jan,then all the related years to which this date belongs to,should be selected.

Any sort of help would be highly appreciated.

Thanks

[Oleg_Troyansky]

Hi,

The issue is related to the date formats. Every time you work with dates, they are uniquely assigned with their respective years. It would be easier to use the fields Year and Month as Dimensions and forget about adding "01-" for each month.

If you calculate the calendar fields in your data, like this:

Calendar:

LOAD

     Date,

     Month(Date) as Month,

    Year(Date) as Year,

...

...

then you could use the fields Year and month as chart dimensions, and you'd get exactly the year over year comparison that you are looking for.

Cheers,

[PM_QlikDev]

Hi Oleg,

Thanks for your inputs. Yes i get your point,but that's the requirement that we have to show our data in the date format as the lowest granuality.so that's why need to show DD-MMM format.Please refer the pic attached.01-is the date shown as an example,similarly for every duplicate record like this,the date should be unique and the respective data should be shown in the same point for both(current and prev year),instead of so many dates apart. Pls let me know if i have made my requirement clear.

[rachel_delany · Accepted solution]

I would create another field in your load script like this:

TEXT(NUM(DAY([Date]),'00'))&'-'&TEXT(MONTH([Date'])) AS DayMonth

[PM_QlikDev · Accepted solution]

Hi Rachel,

It worked ! Thanks a lot.:)