8 Replies Latest reply: Mar 21, 2016 5:22 AM by Nik Bach RSS

    HowTo implement a function independent to the filters

    Nik Bach

      Hello experts,

      I need to implement a function, let’s assume a simple SUM, which works independent to the filters.

      Meaning: even if I use a filter, e.g. Year = 2015, I would still see the SalesData_Sales SUM of 2016.

      All data is in one / the same table.

       

       

      The requirement:

      Screenshot01-Result.png

       

       

      I’ve setup an example with all data already in place, so you can play with it.

        • Re: HowTo implement a function independent to the filters
          Jasper Kouw

          Hi Nik,

           

          You need to use a Set Analysis.

          If I understand you correct your expression becomes SUM({<Year={2015}>}Sales) and SUM({<Year={2016}>}Sales)

           

          Hope This helps.

            • Re: HowTo implement a function independent to the filters
              Nik Bach

              Hi Jasper,

              thanks for the good hint.

              Now it's getting complicated ... I've tried to solve another requirement with the provided help ... but I'm stuck for 2 days, now ...

               

              Requirement: The Result in the Year 2016 = Sum<Sales in 2015> - Sum<Profit in 2014>

              Screenshot01.png !

              If I use the calculation like this ... the year 2016 will not be visible.

               

              Sum({$<SalesData_Year={2015}>} SalesData_Sales) - Sum({$<SalesData_Year={2014}>} SalesData_Profit)

               

               

              How can that be handle in qlik? Does anyone know?

               

              I've uploaded the new file to play with.

               

              Regards

              Nik

                • Re: HowTo implement a function independent to the filters
                  Jasper Kouw

                  Do you have to do this calculation on multiple years or only on the year 2016?

                  Because you don't have the data for 2015 and 2015.

                    • Re: HowTo implement a function independent to the filters
                      Nik Bach

                      Hi,

                      the real calcualtion is much more complicated.

                      I've posted a kind of snippet in order to find out how it actually mus be done if I'm in 2016 and I need to take a value from 2015 and 2014.

                      It seems not to work since the relation to the date of 2014 and 2015 is not available in 2016 ... I guess.

                       

                      I've tried to setup a flag table ... in that table I try to push values from 2014 & 2015 to 2016... somehow

                      I cannot manage to creat a table with flags like this (compare with the screenshot above):

                       

                      SALESDATA_YEAR    SALESDATA_SALES      FLAG

                      2014                              10,00 €                              no      (can create this record, 10€ are from 2014)

                      2014                              20,00 €                              no      (can create this record, 20€ are from 2014)    

                      2014                              30,00 €                              no      (can create this record, 30€ are from 2014)

                      2016                              10,00 €                              yes    (cannot create this record, 10€ are from 2014)

                      2016                              20,00 €                              yes    (cannot create this record, 20€ are from 2014)

                      2016                              30,00 €                              yes    (can notcreate this record, 30€ are from 2014)

                       

                      This way I hope to get this missing link between the years and then I can do the calculation by using the flag table ... but as I've said ... I cannot create such setting of the table in the load-script.

                       

                      Do you think it's possible at all?

                       

                      Reagrds

                      Nik

                • Re: HowTo implement a function independent to the filters
                  Digvijay Singh

                  You need to disregard selection as below in your expression using set analysis -

                  Sum({<Year =>} Sales)

                  • Re: HowTo implement a function independent to the filters
                    Massimo Grossi

                    you can look for "set analysis" in the Qlik help  

                    or here

                    Why is it called Set Analysis?

                    Set Analysis: syntaxes, examples

                     

                     

                     

                    for your .qvw you can start with these expressions

                     

                    Sum(SalesData_Sales)


                    Sum( {$<SalesData_Year= {2015}>} SalesData_Sales)

                    Sum( {$<SalesData_Year= {2016}>} SalesData_Sales)


                    Sum( {$<SalesData_Year= {$(=Max(SalesData_Year))}>} SalesData_Sales)

                    Sum( {$<SalesData_Year= {$(=Max(SalesData_Year)-1)}>} SalesData_Sales)