topic Re: SET ANALYSIS HELP in QlikView

SET ANALYSIS HELP

marikabi — Sat, 16 Nov 2024 02:37:36 GMT

Hello,

I'm trying to add a condition using set analysis to take into account only last 7 days.

This is the formula I'm using to calculate the "utilization" of our shops (and it's working fine):

MAX(AGGR(SUM(CUSTOMERS),PERIOD, SHOP))
/
FirstSortedValue(EMPLOYEES, -Aggr(SUM(CUSTOMERS),PERIOD, SHOP))
/
15

Period is a time dimension in the format: 'DD/MM/YYYY hh:mm:ss' (data are refreshed every 15 minutes)

I already defined a DATE dimension (DD/MM/YYYY) in the loading script.

Please can you guide me how to use set analysis to consider only last 7 days?

Thank you

Re: SET ANALYSIS HELP

sunny_talwar — Tue, 27 Aug 2019 17:36:54 GMT

May be this

Max({<DATE = {">=$(=Date(Today(1)-7, 'DD/MM/YYYY'))"}>} Aggr(Sum({<DATE = {">=$(=Date(Today(1)-7, 'DD/MM/YYYY'))"}>} CUSTOMERS), PERIOD, SHOP)) / FirstSortedValue({<DATE = {">=$(=Date(Today(1)-7, 'DD/MM/YYYY'))"}>} EMPLOYEES, -Aggr(Sum({<DATE = {">=$(=Date(Today(1)-7, 'DD/MM/YYYY'))"}>}CUSTOMERS), PERIOD, SHOP)) / 15

Re: SET ANALYSIS HELP

marikabi — Wed, 28 Aug 2019 06:11:01 GMT

Hi Sunny, that's unfortunately not working.

I tried with a similar structure (but using max(DATE) as function to define the date), and doesn't work either.

Probably I shouldn't put the sets both in inner and outer aggregation, but can't find a combination that's working 😞

Re: SET ANALYSIS HELP

marikabi — Wed, 28 Aug 2019 09:04:36 GMT

Hi Sunny,

I solved using

{<DATE={">=$(=Date(max(DATE)-7,'DD/MM/YYYY'))"}>}

probably just an issue with the spacing 🙂

The issue I have now is regarding the shops which closed -> eg in the last week I don't have any data for a specific shop, so I would expect to have 0% utilization, while I get the utilization for the last 7 days available (meaning 2 weeks ago).

How could I fix this?

Thank you again

Re: SET ANALYSIS HELP

sunny_talwar — Wed, 28 Aug 2019 11:06:51 GMT

Not able to completely understand the problem without seeing it. Are you able to share an example where we can see the issue?

Re: SET ANALYSIS HELP

marikabi — Wed, 28 Aug 2019 11:56:53 GMT

okay, let's assume the last data load happened on June 16th.

the set we put in the function, limits the data to the last 7 days available --> june 10th till june 16th.

Shops A, B and C were regularly open in this period --> the formula works fine, taking into account the period june 10-16.

Shop C was closed in these dates, meaning that I didn't get any record from our database --> formula takes into account the period june 4th till june 10th, showing me the result for this period instead of 0 as I would expect.

Hope is clear now, otherwise will try to fake the data 🙂