Set analysis embedded expression

[#1]

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

[sushil353]

Hi,

Try this:

sum({< [Region Code]={'=$(mid([Territory Code],1,2))' }, [Brand] = {'XXX'}  >} [Sales])

[sushil353]

as per the data provided:

below code is working:

=sum({< [Region]={'$(=mid([Territory],1,2))' }, [Brand] = {'XXX'}  >} [Sales])

HTH

Sushil

[Sokkorn]

Hi Sam,

This should work:

=Sum({$<Region = {$(=Concat(DISTINCT Chr(39) & Region &Chr(39),','))},Brand={'XXX'},Territory=>} Sales)

Regards,

Sokkorn

[jagan]

Hi,

Try this

Create variable with

vSelectedTerritoryCode = MaxString([Territory Code],1,2)

=sum({<[Territory Code]=, [Region Code]={'$(vSelectedTerritoryCode)' }, [Brand] = {'XXX'}  >} [Sales])

Regards,

Jagan.

[#6]

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

[#7]

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

[jagan]

Hi,

Try this

Create variable with

vSelectedTerritoryCode = Mid(MaxString([Territory Code]),1,2);

=sum({<[Territory Code]=, [Region Code]={'$(vSelectedTerritoryCode)' }, [Brand] = {'XXX'}  >} [Sales])

Regards,

Jagan.

[#9 · Accepted solution]

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