6 Replies Latest reply: Oct 13, 2015 4:41 AM by Nir Gil RSS

    Help Scripting

      Hello,

      I have an existing Qlik file, in which I have a chart with the following script (which gather data from different locations):

      Aggr (Sum (Money),[Day/Month/Year],MoneyType)   

      This expression works fine in a line chart (in Which I have different selection lists like MoneyType, Country, City, Date Year, Month, Day

       

      I am\was looking for an expression that present in a text box , when I select a random period, how much Income do I lose from the start date to the end date.

      Now I managed to write an expression in a test environment for a SIMPLE sum expression and It works fine too:

      Sum({<Date={'$(=MIn(Date))'}>} Qty1)

       

      Attached is the test environment which is not 1 to 1 like the production (in which the period selection option is much wider , flexier)

       

      Now to my question-problem , since I am not SO! strong yet in the scripting world.

      I need to write an expression which show, in a text box, the min (earliest) or max (latest) value for my Aggr expression.

       

      I can not attach the real data files.

       

      Your help will be appreciated.

       

      Nir

        • Re: Help Scripting
          Tresesco B

          If I only take

          Nir Gil wrote:

           

          .....

          Aggr (Sum (Money),[Day/Month/Year],MoneyType)

          ....I need to write an expression which show, in a text box, the min (earliest) or max (latest) value for my Aggr expression.

           

          since I did not get it properly (perhaps), you could probably try like:

           

          Max(Aggr (Sum (Money),[Day/Month/Year],MoneyType) )

            • Re: Help Scripting

              Hi,

              Well from some reason when I use the

              the result is that I get the MAX value from selected period instead of the Latest vale , which is what I am looking for and which work fine in my test environment (for the expression: Sum({<Date={'$(=Max(Date))'}>} Qty1)

               

              I assume the Max should be integrated before the Day\Month\Year field :

              Like: Aggr (Sum (Money),MAX[Day\Month\Year,Moneytype)  -  but I can not find the correct script for that.

               

              Nir

                • Re: Help Scripting
                  Tresesco B

                  Could you explain your expected output against a selection in your sample provided?

                    • Re: Help Scripting

                      OK,

                      In my provided sample the results are as expected!

                      I want to show the Latest (Max)  {and the earliest(Min)} value from the selected period which the user select in external year, month and day list boxs. (For example the user can select the last week, April-May 2014, 13-18.6.2015 and set)

                      In my real world, I use a more complicated aggregated-Sum expression.

                      But when I use: Max(Aggr (Sum (Money),[Day/Month/Year],MoneyType) ) the out put is not the latest value (from the selected period) , it is the largest (max) value.

                      The max should be somehow scripted in a condition , I guess, like in the sample result.

                      Nir

                        • Re: Help Scripting
                          Tresesco B

                          May be like:

                          FirstSortedValue( Aggr (Sum (Money),[Day/Month/Year],MoneyType), - [Day/Month/Year])

                           

                          This would give you recent most amount provided you time dimension [Day/Month/year] is a real timestamp/date field.

                            • Re: Help Scripting

                              I appreciate your effort,

                              The suggested expression does not work. (no result is shown)

                              Whereas : Max (Aggr (Sum (Money),[Day/Month/Year],MoneyType)) - works BUT bring the MAX value , not the latest.

                               

                              And Yes - Day/Month/Year is a real date field.