Pivot data? Or sum by subcategory

Tue, 05 Sep 2017 13:27:44 GMT

This seems easy but I just can't seem to figure it out. What follows is sample data but the premise is exactly what I'm trying to accomplish.

I have two tables, Contracts and Animals. On the Contracts table I track all the contracts that I have to use my collection of animals, which animal is used, the total cost of food for the animal during the contract, and the date range of the contract. On the Animals table, I track the percentage of each type of food that each animal eats.

I'm trying to find total food costs by food type and Animal. So I'd like a bar graph that sums up the total amount spent for each of the food categories (Meat, Berries, Milk, Honey, Dry Food) by Animal, by year

So, for instance, I'd like to see that Bears had the following breakdown:

I am able to sum by Animal, but I can't then break it down by the percentage of food type that the animal consumes.

Thanks to anyone that can help!

Here are the tables:

Contracts:

Animal ID	Contract Name	Total Cost of Food	Start Date	End Date
4	Birthday Parties	$120,811.00	9/26/18	9/25/19
3	Circus	$114,418.13	9/26/16	9/25/17
2	Commercial	$50,000.00	9/26/17	9/25/18
1	County Fair	$182,522.00	9/26/16	9/25/17
4	Eating Contests	$120,811.00	9/26/17	9/25/18
2	Herding	$50,000.00	9/26/18	9/25/19
1	Petting Zoo	$110,655.00	9/26/16	9/25/17
4	Population Control	$135,471.00	9/26/16	9/25/17
1	Service Animals	$185,428.00	9/26/18	9/25/19
3	Tours	$110,000.00	9/26/17	9/25/18
1	Walking Service	$185,428.00	9/26/17	9/25/18
3	Zoo Exhibits	$110,000.00	9/26/18	9/25/19

Animals

Animal ID	Animal Name	Meat	Berries	Milk	Honey	Dry Food
1	Dog	20%	0%	0%	0%	80%
2	Cat	40%	0%	30%	0%	30%
3	Lion	100%	0%	0%	0%	0%
4	Bear	30%	20%	0%	40%	10%

Re: Pivot data? Or sum by subcategory

sunny_talwar — Tue, 05 Sep 2017 16:42:01 GMT

It would be better to transform the animal table using The Crosstable Load. Once you do that, all you would need is to use the new field as your dimension and multiple the value with your expression

topic Pivot data? Or sum by subcategory in QlikView

Pivot data? Or sum by subcategory

Re: Pivot data? Or sum by subcategory