New to Qlik Sense

[happyjoshua777]

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

[happyjoshua777]

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

[jonathandienst]

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.

[happyjoshua777]

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