Nested IF Statement

[aagyemang]

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,

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

)

[Gysbert_Wassenaar]

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

[aagyemang]

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.

[Gysbert_Wassenaar]

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

[aagyemang]

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”.

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);

[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.