Text box expression issue

[vijetas42]

Hi All,

I am facing an issue in one scenario where i need to calculate project count where phase should be complete and sales value should be >0

In this scenario if I have two phases for single project then it should consider it as various and should not take count in completed

Project Id     Phase           Sale

1               Complete        13245

1               Inception         124134

2               Complete         124335

2                Cancelled         0

so from above example Id 1 should not get considered because it is having two phase and both are >0 in other side ID 2 should be considered because cancelled phase is having value as 0 so, it should count only completed one.

I have use expression like this,

count(DISTINCT {<[Phase]={'Complete'},[Project Id]={"=sum($(vSales))>0"}>}

if(Aggr(count(DISTINCT [Phase]),[Project Id])=1, [Project Id])) but it didn't work

Please suggest

[vinieme12]

i'm confused, please confirm expected output for the above table

[malini_qlikview]

Hi ,

You can use advanced set analysis,

try this in the expression,

Sum({<[Project Id]={"=(Sum({<Phase={'Complete'}>}Sale)-Sum({<Phase={'*'}-{'Complete'}>}Sale))=Sum({<Phase={'Complete'}>}Sale)"}>} Sale)

Attached is the sample application for the same