19 Replies Latest reply: Jul 6, 2010 6:51 PM by Alex Erwin RSS

    Aggregation Qlikview 9 vs 8.5

      Hi,
      We have application developed in 8.5 and which has expressions using aggr function and It worked fine in 8.5 but when I moved application to Qlikview 9 server and tried to open application crashes or object take like 30 mins to comeback .
      I tried opening other application which don't use aggr function on qlikview 9 and they work fine.
      I thought performance of application in qlikview 9 would be faster than 8.5 but the objects are not even showing up in qlikview 9 .Not sure what is going on.Need help
      sample expression: sum(aggr(TOT_PTNT_CNT,CYM,%_link_Study_GID,[Regimen #],RGMN_GRP_LBL,%_link_Patient_Age,Gender,[%_link_CD_Code],[Diagnosis Combo Desc],[Payment Type],
      %_link_SpecialtyGroup_Code))
      Our data is coming at one level so in order to report at different level we need to aggregate .We have these expressions in 'line charts' and pivot tables and all objects.
      Application size: 6,857 KB
      Fact Rows: 1958596
      Please let me know if some one is experiencing the same problem or if you think I missing something.
      Thanks
      priya
        • Aggregation Qlikview 9 vs 8.5

          Hi Priya

          I have the same problem with V9 using an AGGR function that aggregates across more than one dimension, This has been reported to QlikTech but I have no response from them yet.

          I have found that reducing the AGGR function to aggregate over a single dimension works, but this isn't a solution to the problem, our move to version 9 has been delayed by this whilst we decide if we need the functionality or not.

          Regards,

          • Aggregation Qlikview 9 vs 8.5

            Hi Guys,

            I could be missing something, however I would usually write an aggr expression like this

            aggr(sum(Sales),Region)

            This would aggregate to the region level.

            Try swapping your formula around

            Regards,

            Neil

             

              • Aggregation Qlikview 9 vs 8.5

                My formula is that way round, still have a problem!

                  • Aggregation Qlikview 9 vs 8.5

                    Nigel,

                    Please can you post your expression, just incase i can spot anything......please let me know your measure in case it is not obvious.

                     

                    Regards,

                    Neil

                     

                      • Aggregation Qlikview 9 vs 8.5

                        Hi Neil

                        My expression is a rather complex one, and it is being used as a calculated dimension rather than an expression, it's used in a pivot table because we need to be able to expand/collapse the groups.

                        It is aggregated over two cyclic groups: XXX_CyclicGroup_LPCharts1 & XXX_CyclicGroup_LPCharts2

                        And here it is...........

                         

                        =if(
                        aggr(
                        rank(
                        Sum ( _rFMV )
                        -
                        Sum ( { $ < DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"} > } _rFMV )
                        ,2
                        )
                        ,XXX_CyclicGroup_LPCharts1,XXX_CyclicGroup_LPCharts2) <= YYY_LPCharts_TopBottomN

                        ,'A Top Increase ' &
                        Num (
                        Aggr (
                        Rank (
                        Sum ( _rFMV )
                        -
                        Sum ( { $ < DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"} > } _rFMV ) ,2)
                        ,XXX_CyclicGroup_LPCharts1,XXX_CyclicGroup_LPCharts2
                        )
                        ,000,
                        ),

                        if(
                        aggr(
                        rank(
                        -Sum ( _rFMV )
                        +
                        Sum ( { $ < DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"} > } _rFMV )
                        ,2
                        )
                        ,XXX_CyclicGroup_LPCharts1,XXX_CyclicGroup_LPCharts2) <= YYY_LPCharts_TopBottomN

                        ,'C Top Decrease ' &
                        Num(
                        YYY_LPCharts_TopBottomN
                        + 1
                        - Aggr (
                        Rank (
                        - Sum ( _rFMV )
                        +
                        Sum ( { $ < DT_ReportDate={"$(vCompDate)"},DT_ComparisonDate={"*"} > } _rFMV )
                        ,2)
                        ,XXX_CyclicGroup_LPCharts1,XXX_CyclicGroup_LPCharts2)
                        ,000),
                        'B Other'
                        ))


                         

                        The concept is very simple, I want to identify Top5, Bottom5 and Others.

                        I'm sure there must be an easier way to achieve this, but I'll be damned if I can think of it.

                          • Aggregation Qlikview 9 vs 8.5

                            Hi Nigel,

                            In v9 they have changed the syntax of the set analysis.

                            DT_ComparisonDate={"*"} needs to be changed to DT_ComparisonDate= the {"*"} is no longer needed.

                            The next thing to check is the $ sign expansion please check this DT_ReportDate={"$(vCompDate)"} by taking the label off your expression is it presenting the right data when hovering over the expression, I've had differing experience of this, please can you try this

                            DT_ReportDate={='$(vCompDate)'} or this DT_ReportDate={'=$(vCompDate)'}

                            Try and get a single if statement working.

                            Also be aware of new set analysis functionality (indirect set analysis)

                            The Qlikview Reference manual in v9 has been beefed up on set analysis.

                            Regards,

                            Neil

                              • Aggregation Qlikview 9 vs 8.5

                                Ni Neil

                                Removing the {"*"} makes no difference.

                                $ Sign expansion is working fine.

                                I can't think of a way to turn this into a single IF statement.

                                Not sure if indirect set analysis will work but I'll take a look.

                                 

                                One more clue on this, specific to my formula............

                                Version 9 has a much improved syntax highlighter in the expression window, and it highlights both of my GROUP names as problems (i.e. they are in Red and it says bad field names at the top).

                                  • Aggregation Qlikview 9 vs 8.5

                                    I think we are getting there,

                                    put the group names in square brackets or " "

                                    regards,

                                    Neil

                                      • Aggregation Qlikview 9 vs 8.5

                                        Hi Neil

                                        The syntax checking is now working fine (there was also a comma out of place) but this table still won't calculate in V9 as it runs out of memory, it was fine in V8.5 so I do think something has changed in V9 that causes this.

                                        Cheers

                                          • Aggregation Qlikview 9 vs 8.5

                                            Hi Neil

                                            One more thing.

                                            If I move this crazy formula into the Expressions list it works fine, table calculates really well and really fast, so I am assuming that the formula itself is now working ok, the problem with this implementation is that I cannot now have the Expand/Collapse functionality.

                                            Cheers,

                                              • Aggregation Qlikview 9 vs 8.5

                                                And Finally......

                                                If I move this document onto a 64 Bit Machine with 2 Cores and 8GB Ram, it works, slowly, but it works.

                                                All this has proved though is that we can certainly not use it in production, so either the table functionality has to be removed or we don't go to V9, either of which is not a great option for us.

                                                  • Aggregation Qlikview 9 vs 8.5

                                                    Glad you now have the formula working, Is there any chance of aggregating the data in the script using range sum etc

                                                    • Aggregation Qlikview 9 vs 8.5

                                                      Hi Nigel,

                                                      I just requested qlikview for support.I am thinking AGGR is problem in 9.Not sure what to do.Brain storming

                                                        • Aggregation Qlikview 9 vs 8.5

                                                          Hi

                                                          Qliktech have just "last 20 minutes" come back to me with the following email, so you might go ahead and mention this to them to see if they think it could be applied to your problem, I haven't had a chance to test this yet but will do so in the next 24 hours.

                                                           


                                                          [[Edited: Anyone needing assistance with patches should contact QlikView Support.]]



                                                            • Aggregation Qlikview 9 vs 8.5

                                                              Great Thanks for information.

                                                              Please help me understand

                                                              I am now on VERSION 9.00.7469.8 SR4 64 Bit Edfition (X64)

                                                              Can you please tell if this patch is something new and has to be applied on the one I am using.

                                                               

                                                                • Aggregation Qlikview 9 vs 8.5

                                                                  Hi

                                                                  Yes, this patch is completely new and should be applied to your version, I still haven't done any testing on this yet so you need to be sure to test it thoroughly before moving it into a production environment.

                                                                  Cheers,

                                                                    • Aggregation Qlikview 9 vs 8.5

                                                                      I have just upgraded my x64 Qlikview clients from v8.5 to v9, because I found a significant improvement of performance when utilizing the 'aggr' function in list box objects, and calculated dimensions in charts.

                                                                      I was on 8.50.6231.5, and upgraded to 9.00.7502.8 SR5.

                                                                      On v8.5, clearing all selections in one qlikview application which used these aggr functions took around 60 seconds. On the latest v9 build (7502), it takes around 10 seconds!

                                                                      Priyadarshini, please let us know how you get on with the latest v9 build. For me, the improvement is very impressive.

                                                        • Aggregation Qlikview 9 vs 8.5

                                                          I have an example written in 8.5, however this should be more efficient.

                                                          In Script:

                                                           

                                                           

                                                           

                                                           

                                                           





                                                           

                                                          RANK:
                                                          LOAD * INLINE [
                                                          RANK
                                                          TOP
                                                          BOTTOM
                                                          ALL
                                                          ];

                                                          create list box for RANK (only one value selected)

                                                          In Variables

                                                           

                                                           

                                                           

                                                           

                                                           

                                                           



                                                           



                                                           

                                                           

                                                          vTB =IF(RANK = 'TOP' ,'', IF(RANK = 'BOTTOM','*-1',''))

                                                          replace kpi and dimensions to your kpi and dimensions





                                                           

                                                          =IF(aggr(rank(sum($(vKPI))$(vTB)),@SalesCyclic)<=$(vRank),@SalesCyclic)

                                                          You can try it and benchmark it,

                                                          Regards,

                                                          Neil



                                              • Aggregation Qlikview 9 vs 8.5

                                                Hi Neil,

                                                Thanks for your reply,

                                                If I aggregate with one or two dimensions it works fine.But my requirment is to aggregate with more than one dimension and which is causing problems.