Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
lars_plenge
Partner - Contributor III
Partner - Contributor III

Aggr Nodistinc issue with duplicate dates

Hi

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

  1. 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.

2 Replies
sunny_talwar

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

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

lars_plenge
Partner - Contributor III
Partner - Contributor III
Author

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