12 Replies Latest reply: Aug 9, 2018 12:46 AM by Happy Joshua

# min() does not transform all dates to their StartOfMonth equivalent in AGGR()

Hello,

IN order to aggregate data I used the following expression:

sum(

aggr(

if(

monthstart(min(Total <CustomerId,LocationId> RegistrationDate))=StartOfMonth,1,0

)

,CustomerId,LocationId,StartOfMonth

)

)

The data snapshot has been attached (refer to data_snapshot.png)

Initially, I had data for dates for 2016-2017 and the expression above worked perfectly well.

MIN() converted dates to their StartOfMonth equivalent.

However, when I added dates for the first six months of 2018 (see lower part of data_snapshot.png), only those RegistrationDate values which fall on the first of a given Month were accounted for.

in other words out of about a hundred dates which I added for 2018, only 20 are accounted for in the resulting Pivot Table resulting_table.png), while for 2016 and 2017 I get an accurate count for all Customers who registered throughout 2016-2017.

Note: StartOfMonth is product of MonthStart("Date") whereas Date is a column in the Calendar table which contains a range of all dates for 2016-2018 (refer to date_snapshot)

Question: how can I correct the SUM() expression above to ensure that all CustomerIDs for all years are accounted for for all RegistrationDate\s and not just the ones who happened to had registered on the first date of the month?

(Refer expected_result.png which was calculated using flags and SUM())

Thank you

• ###### Re: min() does not transform all dates to their StartOfMonth equivalent in AGGR()

I am a little confused... what really changed between 2016/2017 and 2018? The dates seems to be randomly spread out in month for 2016/2017 and 2018... why would the expression work for 2016/2017, but not 2018? It seems you understand the difference between the two... would you be able to elaborate on this a little bit more?

• ###### Re: min() does not transform all dates to their StartOfMonth equivalent in AGGR()

Nothing changed between 2016/17, I just added random dates for 2018/01 until 2018/07.

Dates for 2016/17 were also generated at random previously.

The expression worked fine for two yearly periods 2016 and 2017 yet fails to aggregate correctly for the 97 (random) records for the first half of 2018.

thanks

• ###### Re: min() does not transform all dates to their StartOfMonth equivalent in AGGR()

I know you mentioned that StartOfMonth is created as a MonthStart field... but may be add MonthStart to StartOfMonth in the expression just to be sure

sum(

aggr(

if(

monthstart(min(Total <CustomerId,LocationId> RegistrationDate))=MonthStart(StartOfMonth),1,0

)

,CustomerId,LocationId,StartOfMonth

)

)

• ###### Re: min() does not transform all dates to their StartOfMonth equivalent in AGGR()

tried MonthStart(StartOfMonth) and the same output.

I have attached a snapshot (see calendar.png) of qvd which I  generated from Calendar table for testing purposes previously.

As one can see StartOfMonth column was getting populated correctly in any case.

thanks

• ###### Re: min() does not transform all dates to their StartOfMonth equivalent in AGGR()

Nothing seems obviously wrong by looking at your expression and images, would you be able to share a sample to may be help you better?

• ###### Re: min() does not transform all dates to their StartOfMonth equivalent in AGGR()

I have attached the following images with snapshots of data for 2018:

• CustomerDetails_1.png
• CustomerDetails_2.png
• geography.png - refer to lookup() below
• OnlineCustHist.png - customer transactions with LocationIds of the online stores purchases were made from

As I mentioned prev. it is only data for 2018 which is not getting aggregated correctly.

...

CustomerStats:

Year(RegistrationDate) & Num(Month(RegistrationDate)) as YearMonth,

lookup('Country','LocationId',LocationId,'Geography') as RegistrationCountry,

Count(CustomerId) as CountOfCust

Resident

CustomerDetails

Group by

lookup('Country','LocationId',LocationId,'Geography'),

Year(RegistrationDate) & Num(Month(RegistrationDate));

thank you

• ###### Re: min() does not transform all dates to their StartOfMonth equivalent in AGGR()

Would you be able to provide the data you have provided as image as a Excel file? I might be able to load them to see what you mean, because images might take a long time to see what issue you might be having

• ###### Re: min() does not transform all dates to their StartOfMonth equivalent in AGGR()

I have uploaded snapshots of data in Excel

thanks

• ###### Re: min() does not transform all dates to their StartOfMonth equivalent in AGGR()

These are the Customer Ids which were accounted for by the AGGR expression above.

• ###### Re: min() does not transform all dates to their StartOfMonth equivalent in AGGR()

Below are the details for 2018 which were aggregated correctly (refer to customers_accounted_for_2018.png above).

thanks

 CustomerId LocationId FirstName MiddleName LastName Age MaritalStatus Gender YearlyIncome NumOfChildren NumOfChildrenResident EducationLevel IsHomeOwner NumOfCars IsContactByEmail RegistrationDate 14383 400 Bradley NULL Carter 20 M M 15000 3 1 Graduate Degree 1 1 0 3-06-2018 14387 339 Ruby NULL Hawkins 56 M F 9000 3 0 Bachelors 0 1 1 29-03-2018 14390 475 Cooper W Mason 29 M M 96000 3 1 Partial College 0 1 0 12-01-2018 14393 499 Kenyon NULL Henderson 75 S M 29000 0 0 Graduate Degree 1 1 0 8-06-2018 14401 453 Heidi M Tucker 52 M F 57000 2 2 Partial College 1 1 0 24-04-2018 14402 373 Colton NULL Cole 46 M M 100000 0 4 Bachelors 0 0 1 17-04-2018 14412 459 Edan M Cooper 35 S M 110000 3 4 Bachelors 0 0 0 31-05-2018 14415 337 Sonia K Spencer 26 S F 57000 1 0 Bachelors 0 0 1 21-02-2018 14416 339 Halla NULL Higgins 36 S F 49000 4 0 Bachelors 1 0 1 3-03-2018 14419 375 Eleanor NULL Scott 65 S F 118000 4 2 Bachelors 0 1 0 1-01-2018 14423 382 Shana NULL Cole 48 M F 20000 3 1 High School 0 0 1 3-06-2018 14433 496 Cullen A Lloyd 24 M M 107000 0 1 Partial College 0 1 1 20-01-2018 14435 477 Lucas C Barnes 38 S M 122000 2 3 Graduate Degree 0 0 1 25-05-2018 14439 459 Tashya A Williams 24 S F 22000 1 1 Graduate Degree 1 0 1 4-05-2018 14443 323 Rhoda NULL Martin 53 M F 109000 5 5 Partial College 0 0 0 8-02-2018 14452 483 Axel C Chapman 62 M M 58000 2 0 Bachelors 0 0 1 15-03-2018 14464 460 Alden A Perkins 18 S M 31000 2 4 Bachelors 0 1 0 27-03-2018 14465 477 Cade NULL Hawkins 40 M M 109000 0 4 High School 0 0 1 8-01-2018 14470 370 Brielle A Spencer 41 M F 50000 3 0 Bachelors 1 1 0 8-02-2018 14473 434 Savannah G Chapman 75 S F 120000 5 5 Partial College 1 1 1 12-03-2018
• ###### Re: min() does not transform all dates to their StartOfMonth equivalent in AGGR()

Were the dates for 2018 entered in exactly the same format as the dates for the prior years? Perhaps some the dates are not being recognized as dates, and the condition will return false - counting less than expected.

Check that all the dates are proper numeric values - they will right align in a list box.

• ###### Re: min() does not transform all dates to their StartOfMonth equivalent in AGGR()

I did that

for the whole column.

I actually generate a test qvd just for customers registered in 2018 and as one can see QlikView does recongise dates in RegistrationDate correctly (see below)

thanks