Count % by year

[Rsaiq]

Hi All,

I need to show % of opened by year using script in qlikview.

Below is my Dataset 

ID.      Status.       Year.    P

1.         Opened. 2022 A

2.          Closed. 2022 B

3.         Opened. 2022  C

4.         Opened  2022 D

5.          Closed   2022 E

6.           Closed.  2023 F

7.           Closed.  2023 G

8.           Closed.  2023 H

9.            Closed. 2023 I

10.          Opened. 2023 J

11.           Closed. 2023 K

12.          Opened. 2023 L

For Year 2022, we have opened count as 3 and overall we have 5 counts(Opened/Closed)

For Year 2023, we have opened count as 2 and overall we have 7 counts(Opened/Closed)

So Expected result should like below:

Year.        % of Opened

2022.      60%    (=3/5*100)    

2023.       29%   (=2/7*100)

Please look into it.

Thanks in Advance

[QFabian · Accepted solution]

Hi @Rsaiq , check this script,  group by and join, with that, you can create the formula that you need

Data:
Load * INLINE [
ID, Status, Year, P
1, Opened, 2022, A
2, Closed, 2022, B
3, Opened, 2022, C
4, Opened, 2022, D
5, Closed, 2022, E
6, Closed, 2023, F
7, Closed, 2023, G
8, Closed, 2023, H
9, Closed, 2023, I
10, Opened, 2023, J
11, Closed, 2023, K
12, Opened, 2023, L
];

Result:
Load
Year,
count(Status) as Status_Opened
Resident Data
Where
Status = 'Opened'
Group By
Year;

left join
Load
Year,
count(Status) as Status_All
Resident Data
Group By
Year;

Final_Result:
Load
Year,
Status_Opened,
Status_All,
Status_Opened / Status_All as Status_%
Resident Result;

drop table Result;

exit script;

[Rsaiq]

Thanks for the solution @QFabian .It worked perfectly