topic Re: Use Max Date in Set Analysis KPI in QlikView

Use Max Date in Set Analysis KPI

SmartProtection — Fri, 15 Nov 2024 23:25:28 GMT

I read through posts with similar titles, but didn't find any that solved my problem.

I'm trying to post a KPI with the number of active products for which NO new orders have been placed in the past 15 days.

I've created a variable to give me the date 15 days ago, the variable is called literally Date15DaysAgo.

I created the following set analysis using the Max function and my variable, but I'm not getting anything back:

Count(distinct{<max(DateOrderPlaced) = {"<=$(Date15DaysAgo)"}, active = {'1'} >} [ProductID])

Anyone can see where I'm going wrong with this one? Thanks in advance!

Re: Use Max Date in Set Analysis KPI

GaryGiles — Mon, 21 Dec 2020 01:41:11 GMT

You can't use an aggregation function as the left argument in set analysis, so max(DateOrderPlaced) = {"<=$(Date15DaysAgo)"} is a problem.

Try this:

count(distinct {<ProductID=E({1<DateOrderPlaced={">$(Date15DaysAgo)"}>} ProductID),active={'1'}>} ProductID)

It should count ProductiDs that don't have an order placed in the last 15 days by excluded those ProductIDs that have. I excluding greater than as opposed to included less than.

Re: Use Max Date in Set Analysis KPI

SmartProtection — Tue, 22 Dec 2020 12:36:38 GMT

Worked perfectly. Thanks!!