Let's say we have a data table of Meals Served. In Qlikview 11 we have a straight table chart, with Waiter as the dimension. For each Waiter, we can see the number and value of the meals that he/she served.
But now I have to add a new column to this table, showing the value of Meals Eaten by This Waiter. The problem is, I still need the first column in the table to show each Waiter.
Imagine Waiter 1 ate a few meals. But he wasn't the waiter for any of those, so they are not part of the data set in my straight table chart for the row that shows Waiter 1. So I figured I could do Set Analysis to find the value of meals eaten by Waiter 1. Override the dimension and sum up the meals where Waiter 1 is the eater. But as soon as I override the dimension, then Qlikview no longer knows that I am thinking of Waiter 1.
For instance,
sum(if(Waiter <> Eater,mealvalue,0))
doesn't work, that just gives us the meals that this Waiter served, that someone else ate. Since we need to look at meals that this Waiter did not serve, I think we have to do set analysis.
And we can't say Waiter=Eater for the same reason, in my chart that only looks at rows where the Waiter is the server, so it won't bring back any result.
sum({<Waiter=>} TOTAL mealvalue)
sum({1} TOTAL mealvalue)
These don't work, because they add up all the meals for all Waiters. I need each Waiter's eating to show up on "his" row in the chart.
I want to tell Qlikview to add up the meal values eaten by the Waiter on each row of the chart, but those meals are not in the data set for that Waiter's Meals Served.
Does anybody have an idea how to get around this situation? Thanking you in advance for any hints you might have.
.png "Luminary Alumni"){kind=icon}