Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
In my data set, I have 4 regions, each region having 10 territories. I am trying to get a region sales total when a territory is selected by using the mid function as region is always the first 2 characters of territory. Brand XXX is constant. In the example data set, If I filter on Territory A11, I would like to see the total for Region A1 (Sales = 30).
I tried this formula but cannot get it to work. Not married to the mid formula so if there is a better way, I would be most appreciative.
sum({< [Region Code]={'=mid([Territory Code],1,2)' }, [Brand] = {'XXX'} >} [Sales])
Example Data set :
Territory,Region, Brand, Sales
A11, A1, XXX, 10
A12, A1, XXX, 10
A13, A1, XXX, 10
A21, A2, XXX, 20
A22, A2, XXX, 20
A23, A2, XXX, 20
A11, A1, YYY, 100
A12, A1, YYY, 100
A13, A1, YYY, 100
A21, A2, YYY, 200
A22, A2, YYY, 200
A23, A2, YYY, 200
Thanks for any help you can offer
Sam
I could not get that to work but I think I have it by using the aggr function and avoided the substring process. I'm new to qlikview so still learning what all the functions do.
Here is what seems to work:
=aggr(sum( {1< [Brand Name]={'XXX'} >}[Monthly Sales]),[Region Number])
All the help is greatly appreciated. Definitely made me dig into different options and furthered my learning
Hi,
Try this:
sum({< [Region Code]={'=$(mid([Territory Code],1,2))' }, [Brand] = {'XXX'} >} [Sales])
as per the data provided:
below code is working:
=sum({< [Region]={'$(=mid([Territory],1,2))' }, [Brand] = {'XXX'} >} [Sales])
HTH
Sushil
Hi Sam,
This should work:
=Sum({$<Region = {$(=Concat(DISTINCT Chr(39) & Region &Chr(39),','))},Brand={'XXX'},Territory=>} Sales)
Regards,
Sokkorn
Hi,
Try this
Create variable with
vSelectedTerritoryCode = MaxString([Territory Code],1,2)
=sum({<[Territory Code]=, [Region Code]={'$(vSelectedTerritoryCode)' }, [Brand] = {'XXX'} >} [Sales])
Regards,
Jagan.
Sushil and Sokkorn,
Both metrics did not work in my report but did work in the sample data I provided. I believe the issue is that the actual filter I use is a concatenation of the Territory Code and Territory Name ([Territory Code] & ' - ' & [Territory Name}.
When I create a Territory Code list box and use that for filtering, it works.
Using this as the filter results in the territory level value, not region level value. Can I account for this in the metric definition?
Thanks for all of your help!
Sam
Thank you everyone for your help on this. I have tried all 3 formulas and I cannot get them to work when I put it back into my actual report. The issue is tied to the listbox used to filter for territory. I recreated a test qvw and have attached it below. The goal is to get the formula to work when selecting from the concatenate field.
Jagan, I changed the MaxString function to Mid as that is what I think the intention was and there were too many arguments to support that MaxString function.
Thanks!
Sam
Hi,
Try this
Create variable with
vSelectedTerritoryCode = Mid(MaxString([Territory Code]),1,2);
=sum({<[Territory Code]=, [Region Code]={'$(vSelectedTerritoryCode)' }, [Brand] = {'XXX'} >} [Sales])
Regards,
Jagan.
I could not get that to work but I think I have it by using the aggr function and avoided the substring process. I'm new to qlikview so still learning what all the functions do.
Here is what seems to work:
=aggr(sum( {1< [Brand Name]={'XXX'} >}[Monthly Sales]),[Region Number])
All the help is greatly appreciated. Definitely made me dig into different options and furthered my learning