Year Dimension From A Date - Sorting Wrong

[andmartre]

This might have been asked before, so I'm sorry for the duplicate.

I have a date in one column and would like to have the year (ideally year and month) as a dimension on this pivot table. I added a group field and also tried it with a calculated dimension using this formula:

Year(PostingDate)

(PostingDate is the column from the source table)

The table shows the year and does the aggregation fine but the year is not sorted descending. Looking at the documentation it said that the Year fuction returns an integer and so I assumed that the sorting should be fine. 

What am I missing here, what am I doing wrong?

Thank you for any help

[sunny_talwar]

@andmartre Would you be able to share an image or a sample so that we can see the issue?

[Vegar]

In order for Year(PostingDate) to work you need to make sure that PostingDate is a date field. 

If you are unsure about that then do  like this. Lets say your PostingDate for today looks like this: '01/10/2020' . Use the following expression to fetch the year 2020.

Year(Date#(PostingDate, 'DD/MM/YYYY') )

If your PostingDate have another format then change the 'DD/MM/YYYY' accordingly.

[andmartre · Accepted solution]

Hello,

think I did not investigate and try enough combinations out. It turns out that when I change the setting in the Sort tab of the "Chart Properties" dialog then I get the desired result for the year.

I clicked on "Override Group Sort Order" and then keep the "Numeric Value" (Descending) and "Text" (A->Z) ticked. That did the change.

So I don't need to add the month at all.

Thank you.

[andmartre]

I use the basic setting in the Edit Script dialog:

SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';

Data comes from a CSV file and has these date and time format values.

Is that ok?

[Vegar]

@andmartreSorry about the long delay, but I missed the notification about your question

Yes it should be OK. After loading the PostingDate into the data model you could "test" the field by adding it into a filter pane. If it is aligned to the right then it is probably formatted correctly, but if it is aligned to the left then it is considered as a string value and you will need to adjust your script in order to load it as a numeric/date.

[Chanty4u]

can you try your chart sorting tab 

sort by expressions

=Max(Year(PostingDate)

[andmartre]

Thanks, but I think Filter  Pane is only available in QlikSense not in Qlik View, which is what I have.