topic Re: Add distinct to expression in QlikView

Add distinct to expression

Tue, 17 Sep 2013 15:38:42 GMT

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

Re: Add distinct to expression

stigchel — Tue, 17 Sep 2013 15:44:35 GMT

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

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

Re: Add distinct to expression

Gysbert_Wassenaar — Tue, 17 Sep 2013 15:51:55 GMT

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

Re: Add distinct to expression

Fri, 20 Sep 2013 14:42:21 GMT

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