Please Experts. Really Desperate. Can this be done???

[jadams123]

Need help adding data

Hello Experts:

I am a front end user of Qlik. I dont have admin access. 

I have a problem and it goes something like this:

This is my straight table:

The formula to get below info is: Sum({$<Brand={'GREEN'}>}[# Amount])

REGION	Territory	Territory Sales
West	COMBINED	$2831
West	ABC	$594
West	ZZ5	$2810
West	XYZ	$858
West	ZZ3	$1267
West	ZZ2	$1936

I want to do something like this:

=if(

Territory={'COMBINE'},

(Sum({$<Territory={'COMBINE','ABC','XYZ'},Brand={'GREEN'}>}[# Amount]),

Sum({$<Brand={'GREEN'}>}[# Amount])

)

Basically, I want to end up with "Combine" province Sales number as ($2831 + $594 + $858) instead of $2831.

I have also tried this formula but it doesn't work:

=if(

Territory={'COMBINE'},

((Sum({$<Territory={'COMBINE'},Brand={'GREEN'}>}[# Amount]) 

+

(Sum({$<Territory={'ABC'},Brand={'GREEN'}>}[# Amount]) 

+

(Sum({$<Territory={'XYZ'},Brand={'GREEN'}>}[# Amount])),

Sum({$<Brand={'GREEN'}>}[# Amount]))

Dimension Solution: 

I can even work with:

COMBINE + ABC = $3,425 ($594 + $2,831)

COMBINE + XYZ = $3,689 ($858 + 2,831)

I am not sure if there is a solution for this or not but at this time I am willing to try anything. 

Also if you find, comma of brackets mistake do highlight but try to look past it as I have tried to make sure the brackets and commas are correct. 

Thanks, 

J

[jadams123]

Hello Experts:

I am a front end user of Qlik. I dont have admin access. 

I have a problem and it goes something like this:

This is my straight table:

The formula to get below info is: Sum({$<Brand={'GREEN'}>}[# Amount])

REGION	Territory	Territory Sales
West	COMBINED	$2831
West	ABC	$594
West	ZZ5	$2810
West	XYZ	$858
West	ZZ3	$1267
West	ZZ2	$1936

I want to do something like this:

=if(

Territory={'COMBINE'},

(Sum({$<Territory={'COMBINE','ABC','XYZ'},Brand={'GREEN'}>}[# Amount]),

Sum({$<Brand={'GREEN'}>}[# Amount])

)

Basically, I want to end up with "Combine" province Sales number as ($2831 + $594 + $858) instead of $2831.

I have also tried this formula but it doesn't work:

=if(

Territory={'COMBINE'},

((Sum({$<Territory={'COMBINE'},Brand={'GREEN'}>}[# Amount]) 

+

(Sum({$<Territory={'ABC'},Brand={'GREEN'}>}[# Amount]) 

+

(Sum({$<Territory={'XYZ'},Brand={'GREEN'}>}[# Amount])),

Sum({$<Brand={'GREEN'}>}[# Amount]))

Dimension Solution: 

I can even work with:

COMBINE + ABC = $3,425 ($594 + $2,831)

COMBINE + XYZ = $3,689 ($858 + 2,831)

I am not sure if there is a solution for this or not but at this time I am willing to try anything. 

Also if you find, comma of brackets mistake do highlight but try to look past it as I have tried to make sure the brackets and commas are correct. 

Thanks, 

J

[chrismarlow]

Hi,

Calculated dimension, rather than expression;

=if(Match(Territory,'COMBINED','ABC','XYZ')>0,'COMBINED',Territory)

Hope that helps,

Chris.

[jadams123]

No this does work.

I was "combine + Abc" as territory 1 then "combine Xyz" as territory 2 else territory.

[chrismarlow]

First section read like you wanted the three numbers combined … you actually want COMBINE added separately to XYZ and ABC?

[chrismarlow · Accepted solution]

Maybe tweak the dimension & use this expression;

sum({<Territory-={'COMBINED'}>}[Territory Sales])+if(Match(Territory,'XYZ','ABC')>0,sum(TOTAL {<Territory={'COMBINED'}>}[Territory Sales]),0)

[jadams123]

Yes the first section i wanted 3 territories in expressions.

If that cant work then i want in dimensions.

Combine + abc as territory 1.
Combine + xyz as territory 2.

[jadams123]

I found a solution:

=if(match(Territory,'XYZ')>0,Sum(total{$< Territory={'COMBINE'}, Brand={'Brands'}>}[# Amount]), if, if, if...

What this does is that it puts $200 in the formula column. Then I plus Actuals +  Formula column. 

	Formula	Actuals	Col. A+ Col. B
COMBINE		$200.00
XYZ	$200.00	$100.00	$300.00
ABC	$200.00	$200.00	$400.00
EFG	$200.00	$300.00	$500.00

Thanks everyone for you help and support.