Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Lumjing_sangtam
Contributor
Contributor

Count of status from a range of date

Hi  I have a below set of data and I have create a Year filter ( Year is derived from "Expiry Date"). I want to get the count When 2020 is selected in the filter it should count from Max date to the Min date the count of status  Red, Green and Yellow and same with Year 2021, 2022 and 2023. Can you please help.

Expiry DateStatus 2020Status 2021Status 2022Status 2023
3/31/2022RedRedRedGreen 
3/31/2022RedRedRedGreen 
3/31/2022RedRedRedGreen 
10/10/2023RedRedYellow Green 
7/9/2024Yellow RedYellow Green 
7/9/2024Yellow RedYellow Red
7/8/2014Yellow RedYellow Red
1/1/2021    
3/29/2021    
3/31/2021    
3/31/2022RedYellow RedRed
10/10/2023RedYellow RedRed
7/9/2024RedYellow RedRed
7/8/2014RedYellow RedRed
1/14/2020RedYellow RedRed
1/14/2020RedYellow RedRed
10/10/2023RedRedGreen Red
10/10/2023RedRedGreen Red
7/14/2026RedRedGreen Red
7/14/2026RedRedRedRed
10/10/2023RedRedRedRed
3/31/2022RedGreen RedYellow 
3/31/2022RedGreen RedYellow 
3/31/2022RedGreen RedYellow 
3/31/2022RedGreen RedYellow 
10/10/2023RedGreen RedYellow 
10/10/2023RedRedRedYellow 
3/31/2022RedRedRedYellow 
10/10/2023Green RedRedRed
3/31/2022Green RedRedRed
3/31/2022Green RedRedRed

 

4 Replies
Chanty4u
MVP
MVP

Try expression like:

count({<DateField={'$(=Date(max(DateField)))' , '$(=Date(min(DateField)))'}>}Status)

nevilledhamsiri
Specialist
Specialist

Please refer attachment. Straight table will help to sort from max to min date.

Lumjing_sangtam
Contributor
Contributor
Author

@Chanty4u , thanks for the input. I used the below code but the "Expiry date" is not getting selected when I select 2021 in the year filters please see below SS. I want to select "Expiry date" from Max date to Min date when selecting on the year filter. can you please help how to achieve this 

=count({<[Expiry Date]={'$(=[Max Date])' , '$(=[Min Date])'},[Status 2021]={'Red'}>}[Product code])

Capture.JPG

 

Lumjing_sangtam
Contributor
Contributor
Author

I want to be able to be able to use them in filter like when I select 2021. it should give me a count of Red status from max date to min date. can you help to achive this