Sets analysis within multiple if statement

Sun, 19 Feb 2017 14:49:17 GMT

IF ( Type = 'Project' ,

(sum({<[Baseline]=,[Timeline =>}[Finance Amount]))/1000000,

IF ( Type = 'Idea' ,

(sum({<[Baseline]=, [Timeline]= >}[IDEA Total Capital]))/1000000,

If( Type= 'Charter' ,

(sum({<[Baseline]=, [Timeline]= >}[Charter Total Capital]))/1000000

)))

/////////////////////////////////////////////////////////////////////////

I have written the above expression .

There are four table in the data model.

1 ) having project details - from where type is coming from . It has project details where all the projects are present

2) table having finance data for project specific to Type = Project and this finance table has field Baseline and Timeline which is not present in other two finance table (charter and idea finance)

3) Table having finance data for project specific to Type = Charter

4) table having finance data for project specific to Type = Idea

The expression is used to create a bar graph on sheet 2 of the app.

In sheet 1 , I have some charts and filter baseline and timeline that will be applied to all the charts in sheet 1.

I don't want that the baseline and timeline should impact any of the charts in sheet 2.

So I wrote the expression as given above.

Now the problem is when the user selects anything from Baseline filter and comes to sheet 2 . It displays data only which belong to project type and the nothing shows up for charter and idea projects.

Thanks ,

Nitesh

Re: Sets analysis within multiple if statement

vinieme12 — Sun, 19 Feb 2017 15:07:29 GMT

use alternate states

http://www.qlikfix.com/2014/08/12/alternate-states/

try this

IF ( only({1}Type) = 'Project' ,

(sum({<[Baseline]=,[Timeline =>}[Finance Amount]))/1000000,

IF ( only({1}Type) = 'Idea' ,

(sum({<[Baseline]=, [Timeline]= >}[IDEA Total Capital]))/1000000,

If( only({1}Type) = 'Charter' ,

(sum({<[Baseline]=, [Timeline]= >}[Charter Total Capital]))/1000000

)))

can also be written as

= sum({<[Baseline]=, [Timeline]= >} Pick(Match(Type,'Project','Idea','Charter')

,[Finance Amount],[IDEA Total Capital],[Charter Total Capital])

) /1000000

Post a sample app so people here can help you better

Preparing examples for Upload - Reduction and Data Scrambling

topic Re: Sets analysis within multiple if statement in QlikView

Sets analysis within multiple if statement

Re: Sets analysis within multiple if statement