Turn on suggestions

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

Showing results for

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

Showing results for

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!**

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- New to Qlik Sense
- :
- 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

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

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,

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

)

992 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

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

786 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?

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

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

786 Views