Set analysis include all values but modifier is excluding null entries.

[Monkeyb33f]

Hi All,

I'm struggling with some set analysis on ledger entries - it might be quite an easy solution, but I haven't been able to sort it.

I have two tables, LedgerEntries and LedgerBranch. Unfortunately, not all LedgerEntries have a corresponding LedgeBranch, but the analysis I am attempting to do is as follows in two expressions:

Sum({<[Ledger Entry Type]={'DEBIT'},[Ledger Branch Name]-={'Online'}>}[Ledger Amount])

&

Sum({<[Ledger Entry Type]={'CREDIT'},[Ledger Branch Name]-={'Online'}>}[Ledger Amount])

This works to an extent, but it is ignoring all the entries where Ledger Branch Name is null. I've attempted to use isnull() but this is invalid in set analysis. I essentially just want to sum of ALL entries, where the Branch Name isn't Online. As you may have guessed, Ledger Branch Name exists in the LedgerBranch table, and the Ledger Amount & Ledger Entry Type exist in the LedgerEntries table.

Can anyone help?

[agigliotti]

maybe this:
Sum( {< [Ledger Entry Type] = {'CREDIT'}, [Ledger Branch Name] = {"*"}  - {'Online'} >} [Ledger Amount] )

I hope it can helps.

[Monkeyb33f]

Hi,

Thanks for your help, but sadly it appears to still be excluding the entries with a null branch.

[agigliotti · Accepted solution]

yes it does, i'm sorry there was a misunderstanding.
let's try:

Sum( {< [Ledger Entry Type] = {'CREDIT'} >} if( [Ledger Branch Name] <> 'Online', [Ledger Amount] ) )

[Monkeyb33f]

Brill - works exactly like i'd hoped. Thank you!

[Lavanyasreepada]

Hi ... 

Set Analysis

QlikView has a feature called SET ANALYSIS that directs us a way to add context to a dashboard. When we make selections on certain variables, the changes will be reflected in the entire application.

Set analysis is an extremely powerful feature of QlikView, and it’s important that QlikView developers have some exposure to it, and eventually a thorough understanding of set analysis. Set analysis is a powerful tool for comparison. This section serves as an overview of set analysis and a suggestion to explore this feature in greater detail.

Set Analysis Syntax:

Set expressions are always contained in curly brackets:

{ }

A set expression can contain the following elements:

• set identifier

• set operators

• set modifier

1. Set Identifier

The basic element in a set analysis statement is a set identifier. The term identifier refers to identifying the active set. The set identifier is the state of the data set, and is the first element in the set statement (after the curly brackets). The syntax used is as follows, for example, the sum of sales:

Sum ({set identifier}   sales)

There are four common set identifier options:

* {$}: this option designates the current selection set (the default set). An example that shows the sum of sales for the current selection:

Sum ( {$} sales)

* {1}: this option indicates the full set, ignores the current selection, and considers all data. An example that shows the sum of sales for all data:

Sum ( {1}   sales)

* {any book mark}: this option indicates a bookmark (named anything) selection set. An example that shows the sum of sales for the selections saved with the bookmark named 2006 sales:

Sum  ({2006 sales}  sales)

* {$1}: this option designates the previous selection set. Note that the expression  {$0} is the current selection and {$-1} is the forward selection (if a user clicked on the back button). An example that shows the sum of sales for the previous selections:

Explore Curriculum

Sum( {$1}  sales)

* {$-1}: this option designates the forward selection set.

Read More: SET Analysis Syntaxes, Examples in QlikView

I Hope This Information Definitely Helpful for you  

Thanks in Advance 

Lavanya Sreepada