topic Re: Last 2 months data in QlikView

Last 2 months data

sadiaasghar — Sat, 16 Nov 2024 00:15:53 GMT

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)

Re: Last 2 months data

jwjackso — Fri, 24 Jul 2020 18:35:35 GMT

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.

Re: Last 2 months data

sadiaasghar — Sun, 26 Jul 2020 14:33:36 GMT

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)

Re: Last 2 months data

sadiaasghar — Sun, 26 Jul 2020 15:20:51 GMT

The Expression is not returning anything.

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

Re: Last 2 months data

Brett_Bleess — Tue, 11 Aug 2020 19:15:23 GMT

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/SetAnalysis/set-analysis-expressions.htm

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

Re: Last 2 months data

Kushal_Chawda — Tue, 11 Aug 2020 21:45:55 GMT

try below

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

Re: Last 2 months data

sadiaasghar — Wed, 02 Sep 2020 14:19:06 GMT

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

Re: Last 2 months data

Brett_Bleess — Wed, 02 Sep 2020 14:33:20 GMT

@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

Re: Last 2 months data

Kushal_Chawda — Wed, 02 Sep 2020 14:54:08 GMT

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