Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I'm trying to recreate a report we have that does the following.
We have specific certifications that are granted to a person. So some sample data might look like this.
Date | Certificate Name | Person's ID | Status |
---|---|---|---|
2016-01-01 | Cert 1 | 1 | Active |
2016-01-02 | Cert 1 | 1 | Inactive |
2016-01-02 | Cert 1 | 2 | Active |
2016-01-03 | Cert 1 | 1 | Inactive |
2016-01-03 | Cert 1 | 2 | Inactive |
2016-01-03 | Cert 1 | 3 | Active |
What I'm trying to create is a report that gives you unique counts for a specific certificate. So a report that looks something along the lines of this
Year | Month | Day | Active | Inactive |
---|---|---|---|---|
2016 | 01 | 01 | 1 | 0 |
2016 | 01 | 02 | 1 | 1 |
2016 | 01 | 03 | 1 | 2 |
I've spent probably 8-10 hours looking at videos and building simple reports so I just kind of need help getting pointed in the right direction.
Also, is there any way you can force users to choose filters before the data loads? I.e. we have more than just "cert 1" in the database, but I'd like to force them to say, hey, you have to pick one cert, and one cert only, as well as a date and time frame. Is that possible? Thanks,
Use your calendar fields as dimensions (e.g. Date field, or Year, Month, Day) and then two expressions in a straight table chart:
1)
=Count({<Status = {'Active'}>} DISTINCT PersonsIDField)
2)
=Count({<Status = {'Inactive'}>} DISTINCT PersonsIDField)
I want to note I'm mostly lost on how I get the count for active and inactives to work. I've got the date columns working.
Use your calendar fields as dimensions (e.g. Date field, or Year, Month, Day) and then two expressions in a straight table chart:
1)
=Count({<Status = {'Active'}>} DISTINCT PersonsIDField)
2)
=Count({<Status = {'Inactive'}>} DISTINCT PersonsIDField)
Thanks, this should be enough to get me rolling. My scenario is a tad more complicated but I think I can roll from here.
Also, what about being able to force a client to have to pick a filter before hand, is that possible?
The basic idea of Qlik is to load all data necessary for your analysis (considering all users that access this application) into RAM, then filter the data in RAM.
There might be some ways to query the user for a filter selection before doing a reload (non standard ways so to speak), but I wouldn't recommend going into this direction.
That was somewhat my assumption as well. Thanks