4 Replies Latest reply: Aug 30, 2012 7:18 AM by jens.steppe RSS

    Year to date count problem



      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 :



      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.



        • Re: Year to date count problem
          Jonathan Dienst



          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


            • 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

                • Re: Year to date count problem
                  Jonathan Dienst

                  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