7 Replies Latest reply: Jun 9, 2011 6:31 PM by Leonard Short RSS

    aggr function and weekly dimension

      hi guys,


      1) i have been trying to figure out the correct way to use aggr but i seem to be either getting wrong values or invalid values(causing no data to be displayed) in charts...


      i have 3 columns: columnA columnB and lastUpdated...

      i'm supposed to sum(columnA) and divide it by sum(columnB) to get a % and they have to be aggregated by days

      aggr(sum(columnA)/sum(columnB), lastUpdated)


      what is wrong?


      2) i have also been trying to figure out how to list the dimension in working weeks with the latest/current week right at the end of the axis...


      for e.g., if is currently week 3 of the year, the dimensions would be smth like (start) ... ... 46 48 49 50 51 52 1 2 3 (end)


      the chart works fine if i simply use the lastUpdated column, which is a Date type. however, i only want to see the week so i did a week(lastUpdated) but in the end i only got a single 53 in the dimension...


      what is wrong?


      oh by the way, im using qv10 personal edition...


      thanks in advance for any help!

        • aggr function and weekly dimension
          Johannes Sunden

          Hey Axon,


          If you go ahead and attach your .qvw file it would be easier to try and figure out. The expression might fail depending on the expressions making up ColumnA and ColumnB.

            • aggr function and weekly dimension

              hi Johannes thanks for the reply,


              i'm using personal edition so unless you are using the server edition you won't be able to open my file right? and besides there might be some confidentiality issues with my data, is there a way to share my stuffs w/o leaking confidential stuffs?


              oh and some of the values have zeroes, maybe that was y the division may not work for them. is there any workarounds to detect the zeroes?

                • aggr function and weekly dimension
                  Johannes Sunden

                  Hey Axon,


                  For a licensed QlikView user it's not a problem to open documents created with personal edition, so that's fine.

                  When it comes to confidentiality of the data there's a tab under the Settings > Document Properties called Scrambling, where you can scramble any sensitive fields, like customer names etc.

                    • aggr function and weekly dimension

                      i'm able to scamble the data but seem to be unable to upload the file, the progress bar wasn't moving for a long time.


                      i'll try to explain what what those 3 columns are


                      the values of 6 rows are as follow:


                      columnA columnB lastUpdated

                      100         500        6/9/2011

                      250         600        6/9/2011

                      400         500        6/9/2011

                      0            100        6/7/2011

                      25           500       6/7/2011

                      50           1000     6/7/2011


                      so i'm supposed to (100+250+400)/(500+600+500) for 6/9/2011 and (0+25+50)/(100+500+1000) for 6/7/2011


                      so is aggr(sum(columnA)/sum(columnB), lastUpdated) the right way?


                      could you also help me with the 2nd question regarding the weekly dimension



                        • aggr function and weekly dimension
                          Johannes Sunden

                          Is this a straight table?

                          What are the dimensions?

                          What is the expression for Column A/B? Sum(FieldX)?

                          What is the expression for lastUpdated?

                          Or are they just three fields in the document?

                            • aggr function and weekly dimension

                              the expressions for Column A/B and lastUpdated are just themselves, they were not calculated values but just values extracted from the database...


                              it is the final % from sum(A)/sum(B) that matters...


                              the dimension for the daily trends chart is,

                              =if(lastUpdated>=Today()-30 and lastUpdated<=Today(),Day(lastUpdated))


                              the dimension for the weekly trends chart is,

                              =if(lastUpdated>=Today()-365 and lastUpdated<=Today(), Week(lastUpdated))

                              other than the same problem the daily trends chart has, the weekly chart also does not list the week dimension with the current/latest week at the right end of the axis...

                                • Re: aggr function and weekly dimension
                                  Leonard Short

                                  Couple of suggestions, in your load script do something like this:






                                       week(lastUpdated) as updateWeek,

                                       month(lastUpdated) as updateMonth,

                                       year(lastUpdated) as updateYear

                                  from sourcetable;


                                  Then in your chart, use set analysis instead of the aggr() function.


                                  Sum({$<updateYear={$(=max(year(updateYear)))}>} columnA)


                                  Sum({$<updateYear={$(=max(year(updateYear)))}>} columnB)

                                  Now, add the updateYear, Month, & Week fields to list boxes to allow users to dynamically select their time period. Also you can use  Sum({$<updateYear={$(=max(year(updateYear))-1)}>} columnB) to force the calculation to look at the same data from one year ago.


                                  --edit: you can also use the updateYear etc... fields as dimensions within your charts, or add them to a cyclical group and use that as your dimension to easily switch between them.