Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Showing results for

Announcements

Join us at Qlik Connect for 3 magical days of learning, networking,and inspiration! __REGISTER TODAY__ and save!

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Analytics
- :
- Nested IF Statement

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Showing results for

aagyemang

Contributor II

2018-03-07
11:12 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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='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='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='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='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='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='Aug 2018', SavingsAmount, (If(SavingsMonth='Sep 2018', SavingsAmount, (If(SavingsMonth='Oct 2018', SavingsAmount,

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

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

)

1,597 Views

5 Replies

2018-03-07
11:30 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,391 Views

aagyemang

Contributor II

2018-03-09
12:52 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,391 Views

2018-03-14
06:11 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,391 Views

aagyemang

Contributor II

2018-03-15
01:56 PM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

1,391 Views

2018-03-24
02:34 PM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

1,391 Views

Community Browser