topic Re: counting Inactive users for last 90 days in QlikView

counting Inactive users for last 90 days

hathwarjaya — Sat, 16 Nov 2024 00:55:21 GMT

Hello All,

I need your help in one set analysis expression.

I need to calculate the number of users with no activity in the last 90 days.

and plot a line chart with week and month as x axis.

This is how the data looks like.

User	activity	activity date
A	1	1.12.2019
B	2	2.1.2020
C	2	26.1.2020
D	1	5.2.2020
E	3	20.2.2020
F	3	26.3.2020

Please help me with this.

Re: counting Inactive users for last 90 days

sunny_talwar — Thu, 02 Apr 2020 10:44:23 GMT

Based on the input data provided, can you provide the numerical output?

Re: counting Inactive users for last 90 days

hathwarjaya — Thu, 02 Apr 2020 10:58:21 GMT

Hello sunny Thanks for your reply.

we need line graph of daily total amount of unique users that have gone passive = 90 days since have passed from the date when they have been active in any way

based on the above data for today the count would be 2 . user A and B have had no activity since 90 days.

I hope this is what you asked.

Please let me know if you need some more information

Re: counting Inactive users for last 90 days

sunny_talwar — Thu, 02 Apr 2020 14:55:42 GMT

A static count make sense, but you mentioned that you want it on a line chart? That is what is confusing me. If you want this count in a text box then it would make sense.

Re: counting Inactive users for last 90 days

hathwarjaya — Thu, 02 Apr 2020 17:10:53 GMT

Hello Sunny,

Thats what the customer has requested that they want to see the count of inactive users in a line chart for every week .

something like in the below image.

Please let me know how this can be done in the text box for selected time period.

Re: counting Inactive users for last 90 days

sunny_talwar — Thu, 02 Apr 2020 17:22:57 GMT

I would do something like this

Count(DISTINCT {<[activity date] = {"<$(=Date(Today() - 90, 'D.M.YYYY'))"}>} User)

Re: counting Inactive users for last 90 days

sunny_talwar — Thu, 02 Apr 2020 17:26:17 GMT

Attaching a sample for you to look at

Re: counting Inactive users for last 90 days

hathwarjaya — Thu, 02 Apr 2020 18:29:55 GMT

Thanks a lot Sunny.

Really appreciate your help.

So the line graph is not possible for this KPI?

I just wanted to confirm before i answer to the business customer

Re: counting Inactive users for last 90 days

sunny_talwar — Thu, 02 Apr 2020 18:39:59 GMT

I just don't know where will A and B will plot on the graph? Like this?

Re: counting Inactive users for last 90 days

hathwarjaya — Thu, 02 Apr 2020 18:42:22 GMT

Hello sunny plot should not be the individual user ...

x-axis is the date dimension and y-axis is the count of inactive users.

Re: counting Inactive users for last 90 days

hathwarjaya — Fri, 03 Apr 2020 08:44:21 GMT

Hello Sunny,

the set analysis expression you gave is also including the data from long back also

not only just from 90 days back

could you please help me with this?

Regards,

Jaya

Re: counting Inactive users for last 90 days

sunny_talwar — Fri, 03 Apr 2020 11:21:20 GMT

I am not sure I follow, can you show this with an example?

Re: counting Inactive users for last 90 days

Brett_Bleess — Wed, 29 Apr 2020 11:31:17 GMT

What about using a 'flag' field to do the count, so as you load the records, you are checking if the date last entered is within 90 day window or not, and if not, you set the flag field to a 1 else 0, so you can simply sum the flag field as the expression in the line chart at that point I believe. This will not require set analysis in this case, you do the flag set in the script using an 'if' statement there to do the check and you could set the date check as Today()-90 I believe. Hopefully that gives you another avenue to pursue on this one.

Regards,
Brett