Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
sadiaasghar
Contributor
Contributor

Last 2 months data

I am new to qlikview. I am working on a dashboard regarding audit inspections , in which i have to display names of users who haven't submit inspections in last 2 months. Please suggest any ideas how can we do this using chart expression. I am doing this but it gives me names of all the users who haven't submitted inspections

=NullCount(distinct Inspection_ID)

Labels (1)
8 Replies
jwjackso
Specialist II
Specialist II

Try using the E() function in your Set Analysis.

Count({<User=E({1<InspectionDate={">=$(=AddMonths(Float(Today()),-2))"}>}Inspection_ID),Inspection_ID={"*"}>}User)

 

The idea is that the E() will return all users that don't have an Inspection _ID in the last 2 months.  I added Inspection_ID={"*"} to ensure that the user has an inspection.

Replace InspectionDate with whatever date is associated the Inspection_ID.   Check the date format matches your date.

 

sadiaasghar
Contributor
Contributor
Author

Hi jwjackso,
Thanks for the reply. The formula is not returning anything: I have added
my fields in this formula but it's not working:
Count({=$(=AddMonths(Max(INSPECTION_DATE),
-2))"}>}INSPECTION_ID),INSPECTION_ID={"*"}>}USER_NAME)
sadiaasghar
Contributor
Contributor
Author

The Expression is not returning anything.

Count({<USER_NAME=E({1<INSPECTION_DATE={">=$(=AddMonths(Max(INSPECTION_DATE), -2))"}>}INSPECTION_ID),INSPECTION_ID={"*"}>}USER_NAME)

Brett_Bleess
Support (Former)
Support (Former)

The following links should prove useful:

https://community.qlik.com/t5/Qlik-Design-Blog/A-Primer-on-Set-Analysis/ba-p/1468344

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

https://help.qlik.com/en-US/qlikview/April2020/Subsystems/Client/Content/QV_QlikView/ChartFunctions/...

Hopefully that helps you move things forward.  It would likely be most helpful if you attach a sample QVW file otherwise, and if you have sensitive fields, you can use the Settings\Document Properties\Scrambling tab to scramble those prior to attaching.

Regards,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Kush
MVP
MVP

try below

Count({1-$<INSPECTION_DATE={">=$(=AddMonths(Max(INSPECTION_DATE), -2))"},INSPECTION_ID={"*"}>}USER_NAME)

sadiaasghar
Contributor
Contributor
Author

It's not working for me. Please help me anyone.

Brett_Bleess
Support (Former)
Support (Former)

@Kush  Hey man, guessing notifications may still not be working, so just flagging this one for you, hopefully this lets you know the last suggestion did not work.

Cheers,
Brett

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.
Kush
MVP
MVP

@sadiaasghar  try below. If still does  not  work than provide your sample app to look at

Count({1-$<INSPECTION_ID={"*"}>} aggr(only({<INSPECTION_DATE={">=$(=AddMonths(Max(INSPECTION_DATE), -2))"}>}UserName),UserName))