11 Replies Latest reply: Aug 1, 2012 3:15 AM by Syed Khaled Shahbaaz RSS

    Help With Set Analysis

      Hi everyone,

       

      I am trying to calculate the number of Items where an Item with PCatID = 2 or 3 or 4 comes after PCatID =1. (please see the attached document)

      I think I could use a set analysis expression that evaluates as:

       

      count(ItemID) where max(creationdate) for PCatID=2,3 or 4 is greater than max(CreationDate) with PCatID=1.

       

      How can I do this straight and simple?

       

      [file attached]

       

      Regards and Thanks,

       

      -Khaled.

        • Re: Help With Set Analysis
          Stefan Wühl

          Try either

           

          =count({<ItemID = {"=only({<PCatID={1}>}CreationDate) < max({<PCatID={2,3,4}>}CreationDate)"}>}distinct ItemID)

           

          or

           

          =count(aggr(if(only({<PCatID = {1}>}CreationDate)< max({<PCatID={2,3,4}>} CreationDate),ItemID), ItemID))

           

           

          See also attached.

           

          You need to take care that your timestamps are correctly interpreted as such (using e.g. an appropriate standard timestamp format).

           

          Regards,

          Stefan

            • Re: Help With Set Analysis

              Hello Stefan,

               

              Thank you for your response.

               

              Yes, did tweak the time format a little and it works perfectly fine.

               

              Regards & Thanks,

               

              -Khaled

              • Re: Help With Set Analysis

                Dear Stefan,

                 

                Quite some time since the last message, but we have a slight change of requirement and I need help/suggestion.

                 

                In the same expression you mentioned, we now have three dimensions (RegionName, CityName, AreaName instead of ItemCode that we used previously) .

                 

                The modified expression gives the correct result when the dimensions are selected, but otherwise the count of ItemCode when PCatID=1 is greater than the count when PCatID =1 after 2. In short, the required count is greater than what is required with no filters applied, but gives a correct value with the dimension filters applied.

                 

                The expression used is more or less like this:

                 

                =count(aggr(if(only({<PCatID = {1}>}CreationDate)< max({<PCatID={2,3,4}>} CreationDate),RegionName,CityName,AreaName), ItemID))

                 

                Can you please suggest what could be wrong with the result or the expression?

                 

                Regards & Thanks,

                 

                -Khaled.

                  • Re: Help With Set Analysis
                    Stefan Wühl

                    Looking at

                     

                    =count(aggr(if(only({<PCatID = {1}>}CreationDate)< max({<PCatID={2,3,4}>} CreationDate),RegionName,CityName,AreaName), ItemID))

                     


                    I think you want RegionName, CityName, AreaName to be aggr() dimensions, not part of the if() statement, right?

                    Doesn't this gives you an error in expression, because of too many arguments to if()?

                     

                    So maybe try

                    =count(aggr(if(only({<PCatID = {1}>}CreationDate)< max({<PCatID={2,3,4}>} CreationDate),ItemID),RegionName,CityName,AreaName))

                     

                     

                    If this is not the problem, could you upload an updated sample file?

                      • Re: Help With Set Analysis

                        Yes, the reqt. is to get the aggr. as you correctly mentioned...

                         

                        Please find the sample data attatched for your reference.

                         

                        Regards & Thanks,

                         

                        -Khaled.

                          • Re: Help With Set Analysis
                            Stefan Wühl

                            That's quite a big (in size) sample to demonstrate your issue, maybe too big for me to look into your data in detail.

                            Any chance to create a sample like your first one, just extended by the additional dimensions?

                             

                            Besided the size, I can't see an issue with your data (probably because I just haven't fully understood it). Selecting one of the two document bookmarks haven't added some insight for me, too.

                             

                            Could you maybe point me to the issue in more detail? What are you selecting, what are you seeing in which table and what do you expect to see?

                              • Re: Help With Set Analysis

                                Dear Stefan,

                                 

                                It took me a little long, but I now have the same sample in English.

                                 

                                To be precise about the problem, the percentages do not seem to be evaluating correctly.

                                Please find the sample attached.

                                 

                                P.S: The file is quite big for a sample (as you rightly pointed out), but the problem is very barely noticeable when using smaller data samples.

                                 

                                Regards,

                                 

                                -Khaled.

                                • Re: Help With Set Analysis

                                  Dear,

                                  Stefan.

                                   

                                   

                                  Please Can You help us in the above scenario.

                                   

                                  Thanks,

                                  Mukram.

                                    • Re: Help With Set Analysis
                                      Stefan Wühl

                                      I admit I haven't fully understand your data (and the mere mass made it quite hard to look into).

                                       

                                      I think you can try either

                                       

                                      =Count(

                                      (Aggr

                                      (if

                                      (Only({<ItemTransactions.ProcedureCatID = {1}>}ItemTransactions.CreationDate)

                                      <

                                      Max(

                                      {<ItemTransactions.ProcedureCatID={2,3,4,5}>}ItemTransactions.CreationDate)

                                      ,ItemTransactions.ItemCode1),

                                      ItemTransations_TransDirectorateFrom.RegionID,

                                      ItemTransations_TransDirectorateFrom.CityEnName,

                                      ItemTransations_TransDirectorateFrom.DirectorateEnDesc,

                                      ItemTransactions.ItemCode1)))

                                       

                                      or

                                       

                                      =Count(

                                      (Aggr

                                      (if

                                      (Only({<ItemTransactions.ProcedureCatID = {1}>}ItemTransactions.CreationDate)

                                      <

                                      Max(

                                      total< ItemTransations_TransDirectorateFrom.RegionID,

                                      ItemTransations_TransDirectorateFrom.CityEnName,

                                      ItemTransations_TransDirectorateFrom.DirectorateEnDesc>

                                      {<ItemTransactions.ProcedureCatID={2,3,4,5}>}ItemTransactions.CreationDate)

                                      ,ItemTransactions.ItemCode1),

                                      ItemTransations_TransDirectorateFrom.RegionID,

                                      ItemTransations_TransDirectorateFrom.CityEnName,

                                      ItemTransations_TransDirectorateFrom.DirectorateEnDesc,

                                      ItemTransactions.ItemCode1)))