Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bradlocke
Contributor III
Contributor III

Count Data In Most Recent Month Only

I have a spreadsheet that has data similar to below. This data always goes back 24 months and is updated every month. I have been trying to write an KPI expression that counts the number of certificates that occurred during the most recent month with a secondary KPI that shows the difference, up or down, from the month before. March 2021 in this example, so my output would be 4 for the primary KPI. My current expression gives me a KPI output of "1" presumably because there is only 1 occurrence for the most recent date, 03/13/2021. I need all occurrences for the whole month. Here is my current expression for the primary KPI. I have searched and tried everything I could find before asking. Can anyone assist? Thanks! Also due to agency restrictions, I am unable to download and open qvw files.

Count({<SiteName-={'STATEWIDE'},Certificate={'Beginners','Intermediate','Masters'},Date = {"$(=Date(Max(Date)))"}>}Certificate)

Picture1.png

 

Labels (1)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

You need to define an interval for your date modifier. Try something like this.

Count({<SiteName-={'STATEWIDE'},Certificate={'Beginners','Intermediate','Masters'},Date = {">=$(=monthstart(Max(Date)))<=$(=monthend(max(Date)))"}>}Certificate)

View solution in original post

2 Replies
Vegar
MVP
MVP

You need to define an interval for your date modifier. Try something like this.

Count({<SiteName-={'STATEWIDE'},Certificate={'Beginners','Intermediate','Masters'},Date = {">=$(=monthstart(Max(Date)))<=$(=monthend(max(Date)))"}>}Certificate)

bradlocke
Contributor III
Contributor III
Author

Thank you! This worked perfectly.