16 Replies Latest reply: Jun 30, 2011 4:44 AM by anote.sa RSS

    Sum(Descendants) in MDX

      Dear,

       

      I am a newby on QlikView. Please have a look at my application and guild me how to get the result. On SQL Analysis Services, I can get the result by using Sum(descendants,... but on QlikView, I have no idea.

       

      Thank you in advance.

       

      Anote

        • Sum(Descendants) in MDX
          Kaushik Solanki

          Hi,

           

              Replace your second expression i.e. SalesAmount with following expression.

           

              sum({<#Equipment = {">0"}>}SalesAmount)

           

          Regards,

          Kaushik Solanki

            • Sum(Descendants) in MDX

              Hi Kaushik.solanki,

               

              Thank you for your respond. I'm sorry that I gave a bad example. I don't know how to upload the .QVW again so I attached images. In my case is there are two records of customer 1004. How can I get the expected result.

               

              Best Regards,

               

              Anote

               

               

              Source.pngFormula.pngQlikView.png

                • Re: Sum(Descendants) in MDX
                  Nagaian Krishnamoorthy

                  In order to get the last column also per your requirement, use the following expression for the last column

                   

                  Sum(if(#Equipment >0, SalesAmount,0)) / Sum(#Equipment)

                    • Sum(Descendants) in MDX

                      Hi Kaushik.solanki,,

                       

                       

                      sum({<#Equipment = {">0"}>}SalesAmount) / sum(#Equipement)

                       

                      Sum(if(#Equipment >0, SalesAmount,0)) / Sum(#Equipment)

                       

                      Both of them, give me the same result but it is not what I want. This is becasue there is two records of customer 1004.

                       

                      In SQL Analysis Services, I can get the answer by using this MDX.

                       

                      sum(Descendants([Customer].CurrentMember,[Customer].Customer),

                      (iif ([Measures].[#Equipment)] > 0, [Measures].[SalesAmount] / , 0))) / [Measures].[#Equipment]

                       

                      Please advise. I will re-post again in order to attach the .QVW.

                       

                      Best Regards,

                       

                      Anote

                        • Re: Sum(Descendants) in MDX

                          Hi,

                           

                          Please refer to the attachment.

                           

                          I have created one table - "Ravi"

                          You cannot get answer as 400 for Location "1" as Customer 1004 has one of the equipment as 0.

                          As you said, we want to neglect zeroes.

                           

                          Also, you can refer to expressions I have used.

                           

                          I hope this explains solution for you.

                           

                          Let me know further queries if any.

                            • Sum(Descendants) in MDX

                              HI,

                               

                              in January Customer = 1001 and #Equipment = 1 and SalesAmount = 100

                              in January Customer = 1002 and #Equipment = 2 and SalesAmount = 300

                              in January Customer = 1003 and #Equipment = 0 and SalesAmount = 50

                              In January Customer = 1004 and #Equipment = 0 and SalesAmount = 100

                              In Febuary Customer = 1004 and #Equipment = 1 and Sales Amount = 300

                               

                               

                              Is there any possible way to get 400 (not 300) for Customer 1004?

                              I want to group by Customer before checking #Equipemnt.

                               

                              And the expected total = 800 (100+300+400)

                                • Re: Sum(Descendants) in MDX
                                  Kaushik Solanki

                                  Hi,

                                   

                                     Can you please upload the file.

                                   

                                     How to upload the qvw file. ->  When you reply to any post, you will see "Use Advanced Editor"

                                   

                                     Click on it. You will see an option named Attach Files.

                                   

                                     Browse the file from there  and it will be attached to your post.

                                   

                                  Regards,

                                  Kaushik Solanki

                                  • Sum(Descendants) in MDX

                                    "in January Customer = 1001 and #Equipment = 1 and SalesAmount = 100

                                    in January Customer = 1002 and #Equipment = 2 and SalesAmount = 300

                                    in January Customer = 1003 and #Equipment = 0 and SalesAmount = 50

                                    In January Customer = 1004 and #Equipment = 0 and SalesAmount = 100

                                    In Febuary Customer = 1004 and #Equipment = 1 and Sales Amount = 300

                                     

                                     

                                    Is there any possible way to get 400 (not 300) for Customer 1004?

                                    I want to group by Customer before checking #Equipemnt.

                                     

                                    And the expected total = 800 (100+300+400)"

                                     

                                    Hi,

                                     

                                    In that case, you will have to create one more column in Excel.

                                    Those which you want to add can be given no. 1 and neglected can be 0.

                                    This is to identify uniqueness of each line item of database.

                                    e.g.

                                    in January Customer = 1001 and #Equipment = 1 and SalesAmount = 100 and Addition 1 

                                    in January Customer = 1002 and #Equipment = 2 and SalesAmount = 300 and Addition 1

                                    in January Customer = 1003 and #Equipment = 0 and SalesAmount = 50 and Addition 0

                                    In January Customer = 1004 and #Equipment = 0 and SalesAmount = 100 and Addition 1

                                    In Febuary Customer = 1004 and #Equipment = 1 and Sales Amount = 300 and Addition 1

                                     

                                    Now you can create formula based on this Column.