topic Re: Dimension Calculation errors in QlikView

Dimension Calculation errors

Wed, 25 Nov 2020 16:16:04 GMT

Ok, I'm a newbie so here goes my first question in QlikView. I am trying to create a table or chart that will help me analyze data. I have what I think should be the dimension (Year) and multiple expressions (Products 1/2/3/4). When I create the table or chart, it only allows me to select dimension (Year). I don't want that, I want to select any expression that contains "1" as the value. I'd like to make a decision on which expression to analyze by the largest number of values of "1" for that Product. Note; the expression fields only contain a "0" or "1." The raw dataset looks something like the below:

Year	Product 1	Product 2	Product 3	Product 4
2015	0	0	1	0
2015	1	0	1	1
2015	0	1	1	1
2015	1	0	1	1
2015	1	0	1	1

Essentially, I want the final table to look like the below. I want to be able to click on the Sum of the Products individually; 3, 1, 5 or 4 and select those records for analysis in another table. I tried to move the Products into the dimension (so that I could select them) and the Year to the expression but I get Dimension Calculation errors when I try to Sum each of those fields. I would appreciate if someone could advise. Thanks.

Product 1 = 3

Product 2 = 1

Product 3 = 5

Product 4 = 4

Re: Dimension Calculation errors

santiago_respane — Wed, 09 Mar 2016 20:49:46 GMT

Hi Jason,

i dont know if i understood you but i think you need to work with Straight Table and Pivot table in order to achieve what you need.

I've attached a sample application with dummy data for you to see some examples.

Please let me know if this helps and if you need something else.

Kind regards,

Re: Dimension Calculation errors

Wed, 09 Mar 2016 21:33:33 GMT

Thanks for the prompt response Santiago. Except I'm not clear with the table mock up. My table looks like the one I described above where Product 1, Product 2, Product 3 and Product 4 are different fields/columns, each containing values '0' or '1'. So, I don't have a table with a singular 'Product' column and separate Qty column. Does that make sense what I'm trying to do ?

Re: Dimension Calculation errors

santiago_respane — Wed, 09 Mar 2016 22:46:41 GMT

Ok now i got it, you should use crosstable load, this will allow you to pivot your table so that in memory yo have a single column named Product.

Sample of code:

tmpData:

Crosstable (Product, Sales)

Load Year, [Product1], [Product2], Product3, Product4,Product5 resident TEST;

Let me know if this helps.

Kind regards,

Re: Dimension Calculation errors

Thu, 10 Mar 2016 11:03:17 GMT

Perfect. That's just what i need. Thanks again.

Re: Dimension Calculation errors

santiago_respane — Thu, 10 Mar 2016 11:29:16 GMT

Excellent! Im glad to read that.

Please mark the answer as correct in order close the discussion.

Kind regards,