Last 2 months data

[sadiaasghar]

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)

[jwjackso]

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]

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]

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]

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

[Kushal_Chawda]

try below

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

[sadiaasghar]

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

[Brett_Bleess]

@Kushal_Chawda  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

[Kushal_Chawda]

@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))