6 Replies Latest reply: Nov 9, 2017 6:17 PM by Catherine Gillard RSS

    Qlik Sense - using set expressions

    Catherine Gillard

      Hi,

       

      I've set up a text field and I am trying to calculate a total where the MonthYear field equals the value in PreviousMonthYear field.  i.e. I've set up a table that loads a column called PreviousMonthYear which keeps track of what the previous month is (format is MMM-YYYY).

       

      I've tried using a whole heap of expressions but I either get a blank field or a 0.

       

      One of the expressions is:

      Sum({1<MonthYear={ $(=PreviousMonthYear) }>} TOT_INC)

       

       

      Any ideas on what I'm doing wrong and why it won't calculate?

       

      I've also tried

      Sum( {1<MonthYear = {"Aug-2017"}>} TOT_INC)

      which works fine.

       

      So I set up a variable to take the currently selected value but I can't get the following to work:

      Sum( {1<MonthYear = {"$(vPreviousMonthYear)"}  >} TOT_INC)

       

       

      Thanks in advance.

        • Re: Qlik Sense - using set expressions
          Toni Kautto

          I would suggest that you start by exposing the variable vPreviousMonthYear, by applying the measure ='$(vPreviousMonthYear)' in a title, table measure or text object. Does the result have the same format as your static value Aug-2017?


          To reformat the variable, enclose your variable definition in Date(variable_definition, 'MMM-YYYY')

            • Re: Qlik Sense - using set expressions
              Catherine Gillard

              Hi Toni,

               

              I've set up some Text boxes which display:

              =MonthYear

              which displays Aug-2017

              and

              =PreviousMonthYear

              which displays Jul-2017

               

              but I can't get the SUM expression to use these values.

               

              How do I take the text value that I get in the text boxes and incorporate it into the SUM function?

                • Re: Qlik Sense - using set expressions
                  Toni Kautto

                  My assumption is that PreviousMonthYear and MonthYear are fields in your data model. If that is the case then this expression could possibly work.


                  Sum({1<MonthYear={"$(=PreviousMonthYear)"}>} TOT_INC)


                  It would be easier to advice further if you can provide a simple sample app file.

                    • Re: Qlik Sense - using set expressions
                      Catherine Gillard

                      Thanks!  This reply got me thinking more about the formats of the fields I was using.  I've since changed my fields to make sure they are dates and always reference them in the date format.

                       

                      1.  Date1 now comes in as a date...

                      LOAD

                          Date([Month],'MMM-YYYY') as "Date1",

                      ...

                       

                      2.  I've set vCurrentMonth to =Date(Max(Date1),'MMM-YYYY')

                       

                      3.  Now,  Sum( {$<Date1={"$(vCurrentMonth)"}>} [TOT_INC] ) works for me and I just display the Date1 field in

                      Date(Date1,'MMM-YYYY') format when I need to.

                • Re: Qlik Sense - using set expressions
                  Sumanta Kumar Mandal

                  Hi,

                   

                  First take a text object and in the expression put =$(vPreviousMonthYear) and see in which format  month year value is coming then put that variable in the expression.

                  • Re: Qlik Sense - using set expressions
                    Jean-Baptiste COCHOIS

                    Hello,

                    Please translate French - English

                     

                    Pour utiliser des dates je vous propose d'utiliser le plus simple les variables.

                    Comment :

                    Exemple:

                    Dans le script sous les Set on ajoute

                     

                    Let vLastReload = Date(now(), 'DD MMMM YYYY hh:mm');

                     

                    /*CurrentDate*/

                    Let vToday = '=vCurrentDate'; /*Table Sales, Field OrderDate 26/06/2014 sans cela il renvoi la date d’aujourd’hui 2017*/

                    // Let vToday= Date(Today(),'DD MMMM YYYY'); /* Date courante */

                     

                    let vCurrentYear  = '=max(Year)';

                    let vCurrentDate  = '=max(OrderDate)';

                    let vCurrentMonth  = '=month(max(OrderDate))';

                    let vCurrentDay   = '=day(max(OrderDate))';

                     

                    /*PreviousDate*/

                    let vPreviousYear  = '=vCurrentYear-1';

                    let vPreviousDate = '=date(addyears(max(OrderDate),-1))';

                    let vPreviousMonth  = '=month(addmonths(max(OrderDate),-1))';

                    Let vPreviousDay = '=vCurrentDay-1';

                     

                    Attention au respect de l'écriture

                     

                    Ensuite les sommes pour la date courante dans un KPI un pour Year, un pour Month, un pour Day et un histogramme avec une Dimension Hiérarchique : Year, Month, OrderDate... et Expression : Sum(Sales)

                     

                    Le titre pour YTD est ='Year : ' & vCurrentYear, pour MTD ='Month : '& vCurrentMonth, pour DTD=' Day : ' & vCurrentDay.

                     

                    YTD =SUM({<Year={'$(vCurrentYear)'},Month=,OrderDate=>}Sales)

                    MTD =SUM({<Year={'$(vCurrentYear)'},Month={'$(vCurrentMonth)'},OrderDate=, Day=>}Sales)

                    DTD =SUM({<Year={'$(vCurrentYear)'},Month={'$(vCurrentMonth)'},OrderDate=, Day={'$(vCurrentDay)'}>}Sales)


                    Pour PreviousDate on remplace vCurrentDate par vPreviousYear, vPreviousMonth et Si on veut Day-1 vPreviousDay.


                    Month=,OrderDate=,Day= vérouillent la date à l'année, au mois, à day

                     

                    Faites la vérification avec un tableau croisé Dimension :Year, Month et Expression : Sum(Sales) vous verrez si des dates ont une somme=0

                     

                    Enfin pour savoir où vous en êtes :

                    Dans une Zone texte :

                    ='Order Date - ' &' '& If((SUM({<Year={'$(vCurrentYear)'},Month={'$(vCurrentMonth)'},[Invoice Date]=,OrderDate=, Day={'$(vCurrentDay)'}>}Sales)<=0),OrderDate &' '& 'No Sales',

                    If((SUM({<Year={'$(vCurrentYear)'},Month={'$(vCurrentMonth)'},[Invoice Date]=,OrderDate=, Day={'$(vCurrentDay)'}>}Sales)>0),GetFieldSelections(OrderDate)))

                     

                    Autre : Pour une période N et N-1 avec Field = date

                    Code Pour la période N :

                    sum({$ <date={'>=$(=addmonths(min(date))) <=$(=addmonths(max(date)))'}>} valeur)


                    Libelle :

                    ='Sélection (entre ' & date(Min(date)) & ' et ' & date(Max(date)) & ')'

                     

                    Code Pour la période N-1 :

                    sum({$ <date={'>=$(=addmonths(min(date), -12)) <=$(=addmonths(max(date), -12))'}>} valeur)

                     

                    Libellé :

                    ='Sélection N-1 (entre ' & addmonths(min(date), -12) & ' et ' & addmonths(max(date), -12) & ')'

                     

                    Un conseil pour Qlik Sense UTILISER MASTER CALENDAR et supprimer AUTOCALENDAR:

                     

                    Salutations