5 Replies Latest reply: Mar 24, 2018 2:34 PM by Gysbert Wassenaar RSS

    Nested IF Statement

    Adwoa Agyemang

      Hello,

       

      I need some insight on how I can achieve the below express in Qlik that will work.

       

      I am trying to create an expression that will sum the Savings Amount for each month that is less than today.

       

      I was able to get the below expression to work with 2 parameters, but anything more than that I get an expression error.

       

      Another thing to note, I had to unpivot my data during the script load as my original data was in cross table format.

       

      Can anyone advice a solution.

       

      Thank you.

       

      if(if(today()> '01-Jan-2018',(sum(If(SavingsMonth='Jan 2018', SavingsAmount)))),

       

       

      if(today()> '01-Feb-2018',(sum(If(SavingsMonth='Jan 2018', SavingsAmount,

      (If(SavingsMonth='Feb 2018', SavingsAmount

      )))))),

       

       

      if(today()> '01-Mar-2018',(sum(If(SavingsMonth='Jan 2018', SavingsAmount,

      (If(SavingsMonth='Feb 2018', SavingsAmount, (If(SavingsMonth='Mar 2018', SavingsAmount,

      )))))))),

       

      if(today()> '01-Apr-2018',(sum(If(SavingsMonth='Jan 2018', SavingsAmount,

      (If(SavingsMonth='Feb 2018', SavingsAmount, (If(SavingsMonth='Mar 2018', SavingsAmount, (If(SavingsMonth='Apr 2018', SavingsAmount,

      )))))))))),

       

       

      if(today()> '01-May-2018',(sum(If(SavingsMonth='Jan 2018', SavingsAmount,

      (If(SavingsMonth='Feb 2018', SavingsAmount, (If(SavingsMonth='Mar 2018', SavingsAmount, (If(SavingsMonth='Apr 2018', SavingsAmount,

      (If(SavingsMonth='May 2018', SavingsAmount,

      )))))))))))),

       

       

      if(today()> '01-Jun-2018',(sum(If(SavingsMonth='Jan 2018', SavingsAmount,

      (If(SavingsMonth='Feb 2018', SavingsAmount, (If(SavingsMonth='Mar 2018', SavingsAmount, (If(SavingsMonth='Apr 2018', SavingsAmount,

      (If(SavingsMonth='May 2018', SavingsAmount, (If(SavingsMonth='Jun 2018', SavingsAmount,

      )))))))))))))),

       

       

      if(today()> '01-Jul-2018',(sum(If(SavingsMonth='Jan 2018', SavingsAmount,

      (If(SavingsMonth='Feb 2018', SavingsAmount, (If(SavingsMonth='Mar 2018', SavingsAmount, (If(SavingsMonth='Apr 2018', SavingsAmount,

      (If(SavingsMonth='May 2018', SavingsAmount, (If(SavingsMonth='Jun 2018', SavingsAmount, (If(SavingsMonth='Jul 2018', SavingsAmount,

      )))))))))))))))),

       

       

      if(today()> '01-Aug-2018',(sum(If(SavingsMonth='Jan 2018', SavingsAmount,

      (If(SavingsMonth='Feb 2018', SavingsAmount, (If(SavingsMonth='Mar 2018', SavingsAmount, (If(SavingsMonth='Apr 2018', SavingsAmount,

      (If(SavingsMonth='May 2018', SavingsAmount, (If(SavingsMonth='Jun 2018', SavingsAmount, (If(SavingsMonth='Jul 2018', SavingsAmount,

      (If(SavingsMonth='Aug 2018', SavingsAmount,

      )))))))))))))))))),

       

       

      if(today()> '01-Sep-2018',(sum(If(SavingsMonth='Jan 2018', SavingsAmount,

      (If(SavingsMonth='Feb 2018', SavingsAmount, (If(SavingsMonth='Mar 2018', SavingsAmount, (If(SavingsMonth='Apr 2018', SavingsAmount,

      (If(SavingsMonth='May 2018', SavingsAmount, (If(SavingsMonth='Jun 2018', SavingsAmount, (If(SavingsMonth='Jul 2018', SavingsAmount,

      (If(SavingsMonth='Aug 2018', SavingsAmount, (If(SavingsMonth='Sep 2018', SavingsAmount,

      )))))))))))))))))))),

       

       

      if(today()> '01-Oct-2018',(sum(If(SavingsMonth='Jan 2018', SavingsAmount,

      (If(SavingsMonth='Feb 2018', SavingsAmount, (If(SavingsMonth='Mar 2018', SavingsAmount, (If(SavingsMonth='Apr 2018', SavingsAmount,

      (If(SavingsMonth='May 2018', SavingsAmount, (If(SavingsMonth='Jun 2018', SavingsAmount, (If(SavingsMonth='Jul 2018', SavingsAmount,

      (If(SavingsMonth='Aug 2018', SavingsAmount, (If(SavingsMonth='Sep 2018', SavingsAmount, (If(SavingsMonth='Oct 2018', SavingsAmount,

      )))))))))))))))))))))),

       

       

      if(today()> '01-Nov-2018',(sum(If(SavingsMonth='Jan 2018', SavingsAmount,

      (If(SavingsMonth='Feb 2018', SavingsAmount, (If(SavingsMonth='Mar 2018', SavingsAmount, (If(SavingsMonth='Apr 2018', SavingsAmount,

      (If(SavingsMonth='May 2018', SavingsAmount, (If(SavingsMonth='Jun 2018', SavingsAmount, (If(SavingsMonth='Jul 2018', SavingsAmount,

      (If(SavingsMonth='Aug 2018', SavingsAmount, (If(SavingsMonth='Sep 2018', SavingsAmount, (If(SavingsMonth='Oct 2018', SavingsAmount,

      (If(SavingsMonth='Nov 2018', SavingsAmount,

      )))))))))))))))))))))))),

      if(today()> '01-Dec-2018',(sum(If(SavingsMonth='Jan 2018', SavingsAmount,

      (If(SavingsMonth='Feb 2018', SavingsAmount, (If(SavingsMonth='Mar 2018', SavingsAmount, (If(SavingsMonth='Apr 2018', SavingsAmount,

      (If(SavingsMonth='May 2018', SavingsAmount, (If(SavingsMonth='Jun 2018', SavingsAmount, (If(SavingsMonth='Jul 2018', SavingsAmount,

      (If(SavingsMonth='Aug 2018', SavingsAmount, (If(SavingsMonth='Sep 2018', SavingsAmount, (If(SavingsMonth='Oct 2018', SavingsAmount,

      (If(SavingsMonth='Nov 2018', SavingsAmount,(If(SavingsMonth='Dec 2018', SavingsAmount,

      )))))))))))))))))))))))))),

      )

        • Re: Nested IF Statement
          Gysbert Wassenaar

          Sounds like you're trying to do a Year-To-Date calculation. Perhaps this blog post helps: Year-over-Year Comparisons

            • Re: Nested IF Statement
              Adwoa Agyemang

              Hi,

              Let me explain what I am trying to achieve. When visualizing the data in a chart I am able to do Year to Date and Year over Year comparison.

               

              What I would like to do is create a table that sums up the SavingsAmount YTD.

              for example (If(sum(SavingsMonth<Today(), SavingsAmount)).

               

              When loading my data I had to unpivot the data, which has lead to limitation on what I am able to do with my data.

                • Re: Nested IF Statement
                  Gysbert Wassenaar

                  Sorry, I don't understand. Can you give an example with source data and the result table you want?

                    • Re: Nested IF Statement
                      Adwoa Agyemang

                      Hello,

                      View below to see sample of my data

                       

                      Excel Spreadsheet Data:

                      This is the data that I loaded into Qlik. I have to do an unpivot data load so that I could to more with the data. I named the month field as “Savings Month”, and the actual value per month as “Savings Amount”.

                      qlik report 2.png

                       

                      What I would like to do is create a formula that does what the YTD Savings field does. It sums up each month as long as the month end is less than today.

                       

                      Below is the script load that I used:

                      Set dataManagerTables = '','Data';

                      //This block renames script tables from non generated section which conflict with the names of managed tables

                       

                      For each name in $(dataManagerTables)

                          Let index = 0;

                          Let currentName = name;

                          Let tableNumber = TableNumber(name);

                          Let matches = 0;

                          Do while not IsNull(tableNumber) or (index > 0 and matches > 0)

                              index = index + 1;

                              currentName = name & '-' & index;

                              tableNumber = TableNumber(currentName)

                              matches = Match('$(currentName)', $(dataManagerTables));

                          Loop

                          If index > 0 then

                                  Rename Table '$(name)' to '$(currentName)';

                          EndIf;

                      Next;

                      Set dataManagerTables = ;

                       

                       

                      Unqualify *;

                       

                      [Data]:

                      CROSSTABLE ([SavingsMonth],[SavingsAmount],1)

                      LOAD

                      [Reference Number],

                          [Jan-2018] AS [Jan 2018],

                          [Feb-2018] AS [Feb 2018],

                          [Mar-2018] AS [Mar 2018],

                          [Apr-2018] AS [Apr 2018],

                          [May-2018] AS [May 2018],

                          [Jun-2018] AS [Jun 2018],

                          [Jul-2018] AS [Jul 2018],

                          [Aug-2018] AS [Aug 2018],

                          [Sep-2018] AS [Sep 2018],

                          [Oct-2018] AS [Oct 2018],

                          [Nov-2018] AS [Nov 2018],

                          [Dec-2018] AS [Dec 2018];

                      LOAD [Reference Number],

                                      [Jan-2018],

                                      [Feb-2018],

                                      [Mar-2018],

                                      [Apr-2018],

                                      [May-2018],

                                      [Jun-2018],

                                      [Jul-2018],

                                      [Aug-2018],

                                      [Sep-2018],

                                      [Oct-2018],

                                      [Nov-2018],

                                      [Dec-2018],

                      FROM [lib://AttachedFiles/ 2018 Data.xlsx]

                      (ooxml, embedded labels, table is Data);

                        • Re: Nested IF Statement
                          Gysbert Wassenaar
                          What I would like to do is create a formula that does what the YTD Savings field does. It sums up each month as long as the month end is less than today.

                          So, you want a YTD calculation. Correct?

                           

                          Your crosstable load creates the month values as strings. Convert those to date values using the date# function: Date(#Date([SavingsMonth],'MMM-YYYY'),'MMM-YYYY') as SavingsMonth. You'll have to do that in another load that using a resident load from the table you create with the crosstable load. Once you have a proper date field you can do YTD calculations with it: The As-Of Table.