Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
Champion
Champion

Dimension-wise visualization issue

Hi,

I have a barchart where I have to display values from two different tables - the actual values from one, plan values from another.

The table with the actual values has a dimension called "Realization" which is used in the chart.

In the other table I have created a lookalike, but called it differently to avoid the creation of a synthetic key and a loop-link.

For that reason I cannot properly align the bars for the plan values next to the actual values in the chart. I cannot just use another dimension, either.

How can I now make the bar showing the planned values next to the correct one according to this categorization (I have that value in the table with my plan_values, only the fieldname is different)?

Thanks a lot!

Best regards,

DataNibbler

18 Replies
datanibbler
Champion
Champion
Author

So - my budget values are not grouped by product in any way, Year and month in the budget table stand for the exact OrderDate, ProfitCenter and CompanyCode and CustomerID are the same.

So I guess I will have to make a key that is unique in my budget_table and map that against a key that is unique in my fact_table, no - the complete key, not its parts.

Well, that will be a bit, and I'm still not sure about whether to go that way at all - with the other method, albeit potentially less advantageous in the future, I cannot tell yet, I could well find my way alone. And the key in the fact_table would remain unique, though that's just for the looks ...

marcus_sommer

Hi DataNibbler,

in this case you could only compare (and display) sales and budgets on a higher level as productID - this is independent from the used datamodel because it's caused from missing data respectively missing matching data-levels.

An alternatively would be to distribute the aggregated data on the lower levels by populating extra data with any artificial logic maybe by taking the available productID's and adding them to the budget data with a measure like:

1 / count(distinct productID)

but IMO it makes often not much sense.

- Marcus

datanibbler
Champion
Champion
Author

Hi Marcus,

that's just the point. Somewhere I'd have to fake something.

I think for starters I'll just try doing what I know and keeping it altogether separate - then in the chart, instead of using that field as dimension, I'd have to create an inline table with essentially the same values and use this as dimension and use some set_analysis to display the right values on the right dimension_points - that way I can actually put apples and oranges side by side, whatever I want, I've done that before.

Apart from this one table, I have no issue with the budget values, just this one has one more dimension in the chart that's making trouble.

Going forward I'll revisit this, seeing whether this requirement becomes repetitive.

Best regards,

DataNibbler

datanibbler
Champion
Champion
Author

Oh, that's cool - just not now 😉

I have now created an inline table with essentially the same values as the field that was formerly used as a dimension, used this as dimension instead and made two copies of the actual values - one for each one of the two distinct values in there - and put my budget value in the middle - but now QlikView stacks the budget value on top of the actual values with the same dimension 😉

Let's see if that can be fixed, else I'll have to go the way you proposed after all. That also seems possible.

datanibbler
Champion
Champion
Author

Okay - I got that fixed, but it has a pricetag so to speak - I now have double the nr. of values in the legend - one each for "Current" and "Following".

I had to introduce, instead of a field with two distinct values that was formerly used as dimension, an inline table with four values, two each for "Current" and "Following", that way I can have two different bars display side by side within that "group".

Let's see what people say to that - just to have a possible alternative, I'll try out the other way, too.

P.S.: To append that budget data where I have no product-related dimension, I'll just have to make one key for the budget_data containing for instance Year, Month, CompanyCode and Prctr and one for the actual values containing Year, Month, Day, CompanyCode, Prctr and ProductID and then map one to the other.

Ideally - as a matter of fact, the key to the actual values in that fact table is built quite differently and does not contain anything product-specific, either. I will have to try whether I can build a unique key in that way.

P.P.S.: Well, not quite - I do need a few other elements to build a unique key - but the concept would work I guess.

P.P.P.S.: But doesn't that affect performance? That is an extremely long key now ...

datanibbler
Champion
Champion
Author

Hi,

so now I have my generic key with a | in the middle - it kind of looks like this:

>>>   [Key_budget] | [Key_actual] <<< (for the actual values) and

>>> [Key_budget] | NULL() <<< (for the budget values)

whereby the[Key_budget] is about the first half of the [Key_actual].

<=> Now I have to have a unique key t link that "dimensional link table" to my actual central Linktable - but on the other side? Do I need to create two keys to simultaneously link to one actual value and to a budget value?

In two fields or in one? I guess it must be two fields, no? That would necessarily lead to the creation of a synthetic key, though.

Can you help me a little bit with this?

Thanks a lot!

Best regards,

DataNibbler

marcus_sommer

I agree with the suggestion from HIC to avoid a linktable in this case: https://community.qlik.com/blogs/qlikviewdesignblog/2012/10/26/mixed-granularity#comment-68077.

- Marcus

datanibbler
Champion
Champion
Author

Hmmm ... the issue is, the unique key for the actual values consists partly of the unique key for budget values - but it does contain a few more elements, like four or five - so if I was to use just the first part as the key to my central LinkTable, that would link not to one actual and one budget value, but to a whole lot of actuals and one budget value.

So I need that inbetween-link-table with double the nr. of lines that I have in my fact table - to have every key twice, once for exactly one actual value and once more for the corresp. budget value.

I could probably push that "translation" into the central LinkTable, but that would double its nr. of lines - and that one has more than two columns and it links to all the other tables - logically - so there is no knowing what effects it might have if I double the nr. of rows ...

datanibbler
Champion
Champion
Author

Well, since the LinkTable is a fact that I cannot get rid of easily I guess that generic keys are not the best way to go in my situation. I will rather keep budget_data separate and mess around a bit in those few charts where I need to display it alongside fact_data - in most cases, that is easily possible. I had only that one chart, maybe two or three, where I could have well used the common dimension so having the budget_data in my fact_table seemed a good idea - seems like that would be a bit over the top to possibly mess up the entire datamodel ...