I have two dimensions. The first, 'Place' contains only three values (A,B and C) and the other, 'Number' has multiple values. This is how the table looks like for Number 1:
What I want to see, are all values of Place per Number. I know when marking 'Show all dimension values' in the tab Dimensions, I'll get the following result:
Here's the thing: when my first dimension Place has a null value, I want to split the costs proportionally to all dimension values of Place. So all the values, including Place C, should have an addition of 60,60/3. Whatever I do, Place C remains 0. Next to this, I also would like to see that the number next to Place C also shows 1 instead of '-'.
The desired output should then be:
Can anybody help me with resolving this issue?
Thanks a lot!
try with something like this:
LOAD * Resident TABLE Where Len(Trim(Place))>0;
Left Join (OUTPUT_temp)
Total AS Total_add
Left Join (OUTPUT_temp)
Count(DISTINCT Place) as Count
0 as Total_add,
1 as Count
Resident TABLE Where Len(Trim(Place))=0;
DROP Table TABLE;
If(IsNull(Number),1,Number) AS Number,
(Total+Total_add/Count) AS Total
DROP Table OUTPUT_temp;
Thanks for the reply. Unfortunately it still only shows 'A' and 'B' from dimension 'Place'. 'C' also needs to be visible with a third of what the null value presented (i.e. 10). Do you have an idea what we are missing now?
Yes, that's true. But dimension value C has a 0 value in your sample, while it actually has a NULL() value. That's why I still can't see the value and you can. Do you perhaps know a workaround for this issue?
It actually works in the test data. Unfortunately not in my real data. When I make a selection in dimension field Number and select '1', Place 'C' is greyed out already. It just doesn't know this combination of the two dimensions. Maybe this is resolvable by creating a cartesian product. I'll try this and let you know.
Pim, do not forgot to give Giulia credit for the help by using the Accept as Solution button on any of his posts that helped you move things forward. If you do end up doing something different, consider posting that and mark it. If you get further information, leave an update, and we'll see if anyone else has any further ideas, but you may want to consider attaching QVW with what you have next time, as I think it will likely help you get more answers as folks will be able to see the data model and everything, these are tricky to figure out otherwise.
Unfortunately the great help of Giulia didn't result in the answer I wanted to have. However, it was the solution the example needed so I will mark her answer as correct.
I'm afraid attaching a QVW isn't an option, but I can add a QVW of the Excel output the next time.