Show only recent counts in table

[SkitzN27]

Hi Community,

I want to create a table in Qlik Sense that only displays 1 corresponding to the current Phase of the Study (Expected Count Column)

I later plan to convert it to a bar/ pie chart 

The study goes through different phases and may change their phase in the same month.

If a study moves from First Phase  to Second Phase , then I want to make the count of  First Phase = 0 and just display Second Phase= 1 in the table

If a study moves from Second Phase to Third Phase, then the Count for Second Phase =0 and just display Third Phase=1 and so on....

How can I create a table like this, what should be the calculation for count?

(Not in KPI)

Thanks so much,
-N

[Kushal_Chawda · Accepted solution]

@SkitzN27  try below

Data:
LOAD Study,
     Date,
     Phase
FROM Table;

left join(Data)
LOAD Study,
     date(max(Date)) as Date
      1 as Count
resident Data
group by Study;

[kmmqlick]

showing 1 is perfect, but what abt zeros. How to show zeros

[Kushal_Chawda]

Data:
LOAD Study,
     Date,
     Phase
FROM Table;

left join(Data)
LOAD Study,
     date(max(Date)) as Date
      1 as Count
resident Data
group by Study;

Final:
noconcatenate
load Study,
     Date,
     Phase
     alt(Count,0) as Count
Resident Data;

drop table Data;