Dear all,
I have a table "Leased"
| TranDate | LeaseID | DoC | Area | Status |
|---|
| 2017/04/01 | L0001 | 2016/01/01 | 1000 | 1 |
| 2017/05/01 | L0001 | 2016/01/01 | 1000 | 1 |
| 2018/01/01 | L0001 | 2018/01/15 | 1000 | 3 |
| 2018/02/01 | L0001 | 2018/01/15 | 1000 | 3 |
| 2018/03/01 | L0001 | 2018/01/15 | 1000 | 3 |
| 2017/04/01 | L0002 | 2016/01/01 | 2000 | 1 |
I have a query to display all the Lease with Status = 1 and TranDate = '2017/04/01'
Sum({$<TranDate = {'2017/04/01'}, Status = {1}>} Area)
which eq to SQL:
select sum(Area) from table where TranDate = '20170401' and Status = 1
And I want to have 2 formulas which something like this SQL
-- Select area which have other status
select Status, sum(Area) from
(
select distinct LeaseID, DoC, Area, Status from table
where TranDate >= DoC
and Status <> 1
and LeaseID in (select LeaseID from table
where TranDate = '20170401' and Status = 1)
) t
-- Should be 1000
I try this expression
Sum(
Aggr(
Sum({1<
Status -= {1},
LeaseID = P({1<TranDate = {'2017/04/01'}, Status = {1}>} LeaseID),
TranDate = {'>=$(AddMonths(DoC, 0))'}
>}
Distinct LeasedIFA), LeaseID, DoC, LeasedIFA))
The total "LeasedIFA" is correct, but not categorize in correct status
-- Select area which do not have other status
select sum(Area) from table
where TranDate = '20170401'
and Status = 1
and LeaseID not in (select LeaseID from table
where TranDate >= '20170401' and Status <> 1)
-- Should be 2000
And at the end, I want to display a bar chart with group the 2nd SQL into Status 4
Could it happen in Qlik Expression? Or I should do that in SQL first, and import into Qlik?
Thanks and best wishes,
Alex
