6 Replies Latest reply: Mar 31, 2011 11:22 AM by sanchez RSS

    set a value to calculate with

      Hi,

      I'm trying to a create table that calculates automatic. Therefor I've SET a value named Content='5'

      Next I've created two inline table like this:

      LOAD * INLINE [
      content_objtype, name, total
      0, Nieuws, 1000
      1, Voorbeschouwing, 2000
      2, Wedstrijdverslag, 2400
      3, Column, 2345
      4, Cast, 2000
      5, Blaat, 5000
      ];

      LOAD * INLINE [
      content, F2
      1, 2
      2, 4
      3, 6
      4, 8
      5, 10
      ];

      Now I've created a textobject with the syntax::

      =Sum (aantal)/F2

       

      The outcome should be 1474,50 (because the sum total = 14745 and the F2 = 10m so 14745/10

      The problem is that I do not get a value at all. What's wrong?

       

        • set a value to calculate with
          Michael Solomovich

          Richard,
          if you want value of F2 for content=5 (which is 10), replace F2 in your formula with this:
          only({$<content={'5'}>} F2)

            • set a value to calculate with

              Hi Michael.

              What I'm trying to do is set a month. The field Content is an example script.

              So the script must automaticly get the right value from F2. I thought I would set the value to 5 (may) and with this the system calculated the right number. In June I would change the number to 6. My question is how to get this automatic?

              January = 1
              February = 2
              ...
              ...
              December = 12

              Any ideas?

                • set a value to calculate with
                  Michael Solomovich

                  Well, this will be:
                  F2=num(month(today()))
                  So, the previous month is:
                  num(month(addmonths(today(),-1)))

                    • set a value to calculate with

                      Thanks Michael, it almost works as I want it to. The thing is that with the =num(month(today())) formula you get the actual month. But If I open the model next month, the data wil change because of this value without reloading everything.

                      Once a month I update an Olap cube en after that, I reload my Qlikview file that imports the new cumulating data. When I open the QVW once a month after the reload, your formula works great, but when I want to open an older version, the month devider wil obviously change.

                      Is there a way to get Qlikview to know which month number it must calculate with looking to the imported data from the olap cube? The cube does have a date field (2011-01 / 2011-02 / 2011-03 ...) It must get the highest month, so in this example: March (3)

                       

                        • set a value to calculate with
                          Michael Solomovich

                          Richard,

                          I guess you want "current" month not to be defined by the date you open application, but based on the last reload time. If my guess is correct, just change today() to the ReloadTime():
                          =num(month(ReloadTime()))

                          Another possibility is to to get last month from your data. I cannot give you the exact syntax without knowing more details, but the approach will be to create varialble vCurrentMonth in the script based on the last month in your data. It could be something like this, if your month filed in format 'YYYY-MM' is not a date format:


                          data:
                          LOAD
                          ...
                          date(date#(DateField, 'YYYY-MM')) as DateField,
                          ...
                          ;
                          SQL SELECT ... FROM ...;
                          tmp:
                          LOAD
                          num(month(max(DateField))) as CurrentMonth
                          RESIDENT data;
                          LET vCurrentMonth = peek('CurrentMonth');
                          DROP TABLE tmp;


                          And, use vCurentMonth in your front-end calcualtions.