Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Year to date count problem

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.Offlineoffertes.OffNumOfferteKeyoffertes.ItemDesc1Noffertes.Companyoffertes.Offdateoffertes.Versionoffertes.Section
053277HP ZenderREM21-4-200910
053277HP ZenderREM21-4-200911
053277HP ZenderREM21-4-200912

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Year to date count problem

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
4 Replies
MVP
MVP

Re: Year to date count problem

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Year to date count problem

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

MVP
MVP

Re: Year to date count problem

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

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable

Re: Year to date count problem

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

Community Browser