Add distinct to expression

[#1]

Hello,

I have a date field in the format dd.mm.yyyy with many records for the same date.

I would like to draw a chart that would count the total of the records by month (= agregating all records and showing only the total for each month). So I created a dimension like this :

=Date(floor(Date),'YYYY/MM') but I have multiple times the same month (in the x axis)

If possible, I would like to add 'distinct' to this expression to end up with only one total per month.

Any proposition would be greatly appreciated.

Many thanks in advance.

Luc

[stigchel · Accepted solution]

Date is dual, and each combination is stored so use:

date(monthstart(Date), 'YYYY/MM')

[Gysbert_Wassenaar]

Create the monthfield in the script

Load

    Date,

    date(monthstart(Date), 'YYYY/MM') as Month,

     ...other fields here...

from mysource;

And then use the new Month field as dimension

[#4]

Thanks to both of you, this is perfect.

What if I would like this chart not to follow the selection ?

Tried to add {1} but did not work...

Thanks

Luc