Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 | ZZ6 | $1535 |
West | COMBINED | $2831 |
West | ABC | $594 |
West | ZZ5 | $2810 |
West | XYZ | $858 |
West | ZZ4 | $1118 |
West | ZZ3 | $1267 |
West | ZZ2 | $1936 |
West | ZZ | $20 |
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]))
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
Another note:
I dont want to go through dimensions. I have tries this formula and it combines all 3 rows into one. But I want 3 separate rows.
=if(Wildmatch(Territory,'COMBINE',’ABC ,'XYZ'), ‘COMBINE ALL TERRITORIES’,Territory).
For some reason my expressions wont combine the multiple territories.
Thanks,
J