Set analysis restricted by date range

jlcoombs — Sat, 16 Nov 2024 02:47:57 GMT

I have an issue trying to report on data. It has come from 2 separate sources. Prior to a cutover date, I want to use data from one table. After the cutover date, I want to use data from a second table. The tables have similar fields in them. I'm also trying to use a variable to cover a date range as I'm reporting on month to date (vDateSetMTD), last quarter and last 12 months. Cutover date is in 2018.

If I run the query for the current period, my query is returning data from the first table when it shouldn't be.

=(SUM({$<FacilityID={'1'},
Direction={'IN'},
Day={$(vDateSetMTD)}>} ActualValue) // this is the new data table result

+
// this next part uses the old table. It is returning a result for the second part giving me a negative result. It shouldn't return anything at all as vDateSetMTD is the current Month

(SUM({$<LocationID={'2'},
FacilityID={'1'},
Day={'<=$(=Cutover)'},
Day={$(vDateSetMTD)}>} Value) -
SUM({$<LocationID={'3'},
FacilityID={'1'},
Day={'<=$(=Cutover)'},
Day={$(vDateSetMTD)}>} Value)))

In isolation, the first part of the query returns 476, and the second part -628 but it shouldn't be returning anything when vDateSetMTD is July 2019.

Can someone advise what I'm doing wrong?

Re: Set analysis restricted by date range

Brett_Bleess — Mon, 26 Aug 2019 21:01:35 GMT

Jamie, have a look at the following Design Blog post, I think it may help somewhat...

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

There are further links at the bottom of the post, just FYI, and you can back up the URL to Qlik-Design-Blog/ to search on your own as well there if you need some further info that is not in the posts you can get to via the link.

Regards,
Brett

topic Set analysis restricted by date range in QlikView

Set analysis restricted by date range

Re: Set analysis restricted by date range