Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
aagyemang
Contributor II
Contributor II

Nested IF Statement

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,

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

)

5 Replies
Gysbert_Wassenaar

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


talk is cheap, supply exceeds demand
aagyemang
Contributor II
Contributor II
Author

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?


talk is cheap, supply exceeds demand
aagyemang
Contributor II
Contributor II
Author

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

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.


talk is cheap, supply exceeds demand