Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have to compare some values with last year. My customer have created a calendar that is showing which dates to compete with.
| 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.
I don't know why you need Aggr() function here. Have you tried this:
Sum({<KEY_Date = {42431,42432}>} ITEM_COUNT)
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