Aggr Nodistinc issue with duplicate dates

lars_plenge — Mon, 14 Mar 2016 12:33:15 GMT

I have to compare some values with last year. My customer have created a calendar that is showing which dates to compete with.

Calender.Date	HolidayMap
01-02-2016	02-02-2015
02-02-2016	03-02-2015
03-02-2016	04-02-2015
04-02-2016	05-02-2015
05-02-2016	30-01-2015
06-02-2016	31-01-2015
07-02-2016	01-02-2015
08-02-2016	02-02-2015
09-02-2016	03-02-2015
10-02-2016	04-02-2015
11-02-2016	05-02-2015
12-02-2016	06-02-2015
13-02-2016	07-02-2015

Some of the dates are duplicate in the holidayMap.

I can find all the dates in the holidayMap by using a Concat:

Concat({<Calender.Date={">=$(=date(MonthStart(Today()-1))) <=$(=date(Today()-1))"}>}num(HolidayMap), ',')

The issue is when I use the dates in a SUM

sum(aggr(NODISTINCT sum ({<KEY_Date = {42431,42432,42431}>}ITEM_COUNT),Calender.Date))

Date	value
42431	10
42431	10
42432	15

The result is only 25 because the duplicated date is ignored.

How can I fix my expression so I get 35.

Re: Aggr Nodistinc issue with duplicate dates

sunny_talwar — Mon, 14 Mar 2016 14:16:10 GMT

I don't know why you need Aggr() function here. Have you tried this:

Sum({<KEY_Date = {42431,42432}>} ITEM_COUNT)

Re: Aggr Nodistinc issue with duplicate dates

lars_plenge — Mon, 14 Mar 2016 14:29:18 GMT

Hi Sunny

I need 42431 twice when I make Month to date last year. The customer is comparing both 01-02-2016 and 08-02-2016 to 02-02-2015 so I need to have 02-02-2015 twice in MTD LY for February.

regards

Lars

topic Re: Aggr Nodistinc issue with duplicate dates in QlikView

Aggr Nodistinc issue with duplicate dates

Re: Aggr Nodistinc issue with duplicate dates

Re: Aggr Nodistinc issue with duplicate dates