Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
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