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: 
happyjoshua777
Creator
Creator

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

12 Replies
happyjoshua777
Creator
Creator
Author

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

thanks

  

CustomerIdLocationIdFirstNameMiddleNameLastNameAgeMaritalStatusGenderYearlyIncomeNumOfChildrenNumOfChildrenResidentEducationLevelIsHomeOwnerNumOfCarsIsContactByEmailRegistrationDate
14383400BradleyNULLCarter20MM1500031Graduate Degree1103-06-2018
14387339RubyNULLHawkins56MF900030Bachelors01129-03-2018
14390475CooperWMason29MM9600031Partial College01012-01-2018
14393499KenyonNULLHenderson75SM2900000Graduate Degree1108-06-2018
14401453HeidiMTucker52MF5700022Partial College11024-04-2018
14402373ColtonNULLCole46MM10000004Bachelors00117-04-2018
14412459EdanMCooper35SM11000034Bachelors00031-05-2018
14415337SoniaKSpencer26SF5700010Bachelors00121-02-2018
14416339HallaNULLHiggins36SF4900040Bachelors1013-03-2018
14419375EleanorNULLScott65SF11800042Bachelors0101-01-2018
14423382ShanaNULLCole48MF2000031High School0013-06-2018
14433496CullenALloyd24MM10700001Partial College01120-01-2018
14435477LucasCBarnes38SM12200023Graduate Degree00125-05-2018
14439459TashyaAWilliams24SF2200011Graduate Degree1014-05-2018
14443323RhodaNULLMartin53MF10900055Partial College0008-02-2018
14452483AxelCChapman62MM5800020Bachelors00115-03-2018
14464460AldenAPerkins18SM3100024Bachelors01027-03-2018
14465477CadeNULLHawkins40MM10900004High School0018-01-2018
14470370BrielleASpencer41MF5000030Bachelors1108-02-2018
14473434SavannahGChapman75SF12000055Partial College11112-03-2018
jonathandienst
Partner - Champion III
Partner - Champion III

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
happyjoshua777
Creator
Creator
Author

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

customerdetail_2018.png