Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
jlcoombs
New Contributor II

Set analysis restricted by date range

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?

1 Reply
Support
Support

Re: Set analysis restricted by date range

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

To help users find verified answers, please don't forget to use the "Accept as Solution" button on any posts that helped you resolve your problem or question.