Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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