Year to date count problem

[#1]

Hello,

Im having a strange problem consiring a count.

When i do a normal count I get the correct result, when i do a count with an if in i get more then i should have.

My Data :

offertes.Offline	offertes.OffNum	OfferteKey	offertes.ItemDesc1N	offertes.Company	offertes.Offdate	offertes.Version	offertes.Section
0	53	2	77HP Zender	REM	21-4-2009	1	0
0	53	2	77HP Zender	REM	21-4-2009	1	1
0	53	2	77HP Zender	REM	21-4-2009	1	2

When I do :

          - Count(offertes.Offline) = 3

          - count( if( offertes.Offdate <= AddMonths(vReloadDate, 12 * (YEAR- year(vReloadDate))), offertes.Offline)) = 6

I am aware that there is a MonthToDate function, but I created this before I was aware of that.

If vReloadDate = 01/12/2012, YEAR = 2009

Then this function (AddMonths(vReloadDate, 12 * (YEAR- year(vReloadDate)))) will modify the vReloadDate to 01/12/2012.

I hope someone knows the answer, I'm looking for days for this.

Thanks.

[jonathandienst]

Hi

Assuming that vReloadDate and YEAR are behaving correctly, then this looks like a model structure issue. The first statement is looking at a single table, but I suspect that offertes.OffDate and/or YEAR are in a table different to offertes.Offline.

Then the problem is that the association between the tables is not one to one, so that there may be two rows in the second table for each row in the first. When you incorporate a field from the second table in the expression, it will count the expression for each row in each table, hence 6 in the second count.

If that does not help, then I suggest that you post your model or a sample so that it can be looked at in more detail.

Hope that helps

Jonathan

[#3]

Hi Jonathan.

Year is indeed in a different table, but offertes.offdate is not. I added the qvw so you can see for yourself. Maybe there are things I did not mention for you.

Thank in advance

[jonathandienst · Accepted solution]

Quick and dirty analysis - the expression routes through to the calender to get to JAAR

     Tables offertes has 78400 rows, but only 21824 distinct values of OfferteKey

     The link table has 37984 rows with values of OfferteKey, so some values appear more than once in the link table

Because the OfferteKey is not unique in the link table, it will count multiple possible values if you go via the LinkTable to execute the expression.

Another way of structuring your model might have been to concatenate the Orders, Offertes and Invoices table into a single fact table, or if that is not possible, to have separate calendars for each table. If you want to use a link table, then ensure

Regards

Jonathan

[#5]

Thank you Jonathan, your conclusion was correct indeed. The offerteKey was not unique enough, when adjusting this everything was working properly.