Sets analysis within multiple if statement

[#1]

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

[vinieme12 · Accepted solution]

use alternate states

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

OR

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

OR

Post a sample app so people here can help you better

Preparing examples for Upload - Reduction and Data Scrambling