topic Re: Set analysis embedded expression in QlikView

Set analysis embedded expression

Fri, 22 Aug 2014 07:24:35 GMT

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

Re: Set analysis embedded expression

sushil353 — Fri, 22 Aug 2014 07:28:09 GMT

Hi,

Try this:

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

Re: Set analysis embedded expression

sushil353 — Fri, 22 Aug 2014 07:34:08 GMT

as per the data provided:

below code is working:

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

HTH

Sushil

Re: Set analysis embedded expression

Sokkorn — Fri, 22 Aug 2014 07:38:37 GMT

Hi Sam,

This should work:

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

Regards,

Sokkorn

Re: Set analysis embedded expression

jagan — Fri, 22 Aug 2014 07:56:07 GMT

Hi,

Try this

Create variable with

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

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

Regards,

Jagan.

Re: Set analysis embedded expression

Fri, 22 Aug 2014 08:58:42 GMT

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

Re: Re: Set analysis embedded expression

Fri, 22 Aug 2014 15:47:19 GMT

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

Re: Re: Set analysis embedded expression

jagan — Fri, 22 Aug 2014 16:19:14 GMT

Hi,

Try this

Create variable with

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

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

Regards,

Jagan.

Re: Set analysis embedded expression

Fri, 22 Aug 2014 16:38:00 GMT

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