Set Analysis solution?

[stevelord]

Hi Community,

I have a straight table chart that looks like this:

Dimensions: ID and Name

Expression: SUM(Count)

ID     Name     TotalCount

123     Joe        5

456     Mary     10

I have a list box for date logged and I was trying to figure out how to keep people in the above table if I select a range of dates and the TotalCount is 0 due to lack of records in that date range.  So if I select DateLogged 1/1/2010-1/3/2010 on a list box looking at a datatable like below, I want it to flip above to show 5 for Joe and 0 for Mary.

ID     Name     DateLogged     Count

123     Joe     1/1/2010          1

123     Joe     1/2/2010          2

123     Joe     1/3/2010          2

456     Mary     2/4/2010     10

Data set is too big for me to populate all the dates on everyone with concatenation, and that might mess up other calculations related to how many days people logged; also master calendars don't seem to take for people with no date value due to no activity logged in that date.  I can filter in script for perfect results all day long, but intended end user can't (intended end user population pushing back suggestion of vlookups too).

Now, could I per chance put that date logged listbox into an alternate state I make up, like datestate or whatever, then make my sum(TotalCount) expression look into that alternate state, and just give nulls or 0s in that expression column, without making the whole table shrink if people have nulls?  (Maybe wrap that expression in an if statement to force it to say 'no data' or something if the chart tries to ignore my unchecked suppress 0/missing values boxes.) Could someone provide me an example of such an expression if this is the right approach?

Resulting table if I select the January DateLoggeds would look like this

ID     Name     TotalCount

123     Joe        5

456     Mary     0 (or 'no data')

[Gysbert_Wassenaar]

See attached qvw.

[stevelord]

Thanks Gysbert, this is great and I'm confident I can adapt it to my dashboard.  Biggest breakthrough on this front was for me to quit messing with calendars and recall the alternate states. (Then someone to remind me simply how to make that work.)

[stevelord]

Actually, it's close but it's overriding all of the other field selections like client name.

So if the two people in the dummy data were from client A, and I had a billion other people in client B, I get a billion and two people no matter which client I select.  Is there a way to let the table be responsive to other fields, and just not collapse the 0s when the number of steps walked are not present in the given date range?

In attached, I added a list box for ID to pretend that is clients.  So trying to see if I can get the straight table to collapse when id 123 is selected or 456, but not collapse when january dates are selected or february.  Pretty sure the final solution will require me to wander around many boxes or modify the date selection box state- I don't mind if the steps date box is in a state of its own and the formula is referencing that.  I'll do some trial and error on my own and post here if I get a final solution.  (I almost crashed the server just putting the little bit at the end of the expression though and need to be slow and careful with further experiments.   Our dashboard is locked to have always 1 client selected to prevent overwhelming server with people trying to get everything in one punch.)

[Gysbert_Wassenaar · Accepted solution]

Actually, it's close but it's overriding all of the other field selections like client name.

change the {1} to {< Field1=, Field2= >} so that only the selections in Field1 and Field2 are overridden.

[stevelord]

THANK YOU!  This was the final expression: sum(Count)+Sum({<Count=, DateLogged>} 0)

So it lets the list collapse if other fields, like client name or people's names are not present in the selection, but puts a 0 if the only missing data is in the count or date logged fields.  (DateLogged was only populating if people did something, so both it and the count would be missing if nobody did something.  Other stuff like namewas just brought in from a user data table that was joined to the logging count table on user's id.)

I did test by adding a couple more people to the january group and id 123.  When I selected the january dates, everyone including 0s was on there.  When I selected id 123, the id 456 person fell off the list, but the 0 people in id 123 remained, which was the desired result.  Likewise, when I clicked on individual names in 123, the qlikview drilled down like I'd want it to again.  (And when I clicked on the name of a person with 0 count, the datelogged selections opened back up to ones they had in their record, which was proper behavior for a qlikview too.)

Tested backwards and forwards on my own desktop with the little qvw you handed me and confident I won't crash the server when I introduce the +Sum({<Count=, DateLogged>} 0) modification to my expressions.   (I also tested behavior of the id box with 'always one selected value' checked to be sure that doesn't uncheck itself or get bypassed by the other selections.)

[stevelord]

also noticed I was missing an = sign after DateLogged above, but no = sign needed either way apparently for my particular purpose.  (I tested with and without = signs and results appear identical.)