4 Replies Latest reply: Mar 7, 2014 10:51 PM by jay chen RSS

    How to use aggr function in ad-hoc report

      Hi all,

       

      I am just beginner in qlikview, I got an issue when create an ad-hoc report. The report works well with all enabled dimensions, but if we adjust the number of dimensions, the cost value not correct anymore. Because we don't have exactly amount for each demo so in each demo we want to show the value of 'All Demo' Value, please refer to attached file for more detail.

       

      Any help will be appreciated much!

        • Re: How to use aggr function in ad-hoc report
          Tresesco B

          May be like attached sample?

           

          If not, let know the exact output you are looking for.

          • Re: How to use aggr function in ad-hoc report
            Sudeep Mahapatra

            In your chart currently you are showing the value of All Demo (500000). do you want to show something else. I see that you have zero for all other Demos except All Demos.

            Can you please explain me when you say "Adjust the number of dimensions" do you mean adding more dimensions or something else?

              • Re: How to use aggr function in ad-hoc report

                Hi Tresesco & Sudeep,

                 

                When user check the client dimension, I want the output like this:

                 

                ABCC1AD15-2550000055000
                ABCC1AD25-3550000020000
                ABCC1AD35-4550000020000
                ABCC1AD45-5550000012000
                ABCC1AD7-1550000010000
                ABCC2AD15-2550000055000
                ABCC2AD25-3550000020000
                ABCC2AD35-4550000020000
                ABCC2AD45-5550000012000
                ABCC2AD7-1550000010000
                XYZC3AD15-2550000055000
                XYZC3AD25-3550000020000
                XYZC3AD35-4550000020000
                XYZC3AD45-5550000012000
                XYZC3AD7-1550000010000
                XYZC4AD15-2550000055000
                XYZC4AD25-3550000020000
                XYZC4AD35-4550000020000
                XYZC4AD45-5550000012000
                XYZC4AD7-1550000010000

                 

                When user uncheck the client dimension, I want the out put like below:

                 

                ABCAD15-251000000110000
                ABCAD25-35100000040000
                ABCAD35-45100000040000
                ABCAD45-55100000024000
                ABCAD7-15100000020000
                XYZAD15-251000000110000
                XYZAD25-35100000040000
                XYZAD35-45100000040000
                XYZAD45-55100000024000
                XYZAD7-15100000020000

                 

                I can handle this with an If condition,

                 

                If client is checked then:

                Aggr(NODISTINCT Sum({<[Demo]={'All Demo'}>} Cost), Network, Client)

                else

                Aggr(NODISTINCT Sum({<[Demo]={'All Demo'}>} Cost), Network)

                 

                But in reality, there are more than 10 dimensions for user to select, I cannot cover the selected dimensions in Aggr function. Do we have any idea to solve this?