topic Help with expression in QlikView

Help with expression

Anonymous — Thu, 23 Feb 2012 16:52:42 GMT

Hi all,

Please help me with expression in Dimension,

IF(Brand<>'A' and Brand<>'B', if(len(trim(Var1))>0, Country, TOTAL country)

where Var1 = O, E, K

Country =Q,W, R, Other.

I need, if Brand not equal A or B, show all countries, else show TOTAL (Q,W,R, Other)

Thanks,

Help with expression

Thu, 23 Feb 2012 17:06:40 GMT

You want this as a dimension?

What countries would you want to show if it does equal A or B?

What are the expression(s) you are using?

Help with expression

Anonymous — Thu, 23 Feb 2012 18:26:26 GMT

Yes, I have this chart and it work well. But a client wants to change a bit.

If A or B, it should be 1 comumn wich is TOTAL of all countries.

I use this expression

1. IF(Country<>'Other',Count({<Profile={'L'},MYear={'$(=MonthName(max(MYear)))'}>}Country))/

Count({<MYear={'$(=MonthName(max(MYear)))'}>} TOTAL <Country,MYear> Country)

2. IF(Country<>'Other',Count({<Profile={'B'},MYear={'$(=MonthName(max(MYear)))'}>}Country))/

Count({<MYear={'$(=MonthName(max(MYear)))'}>} TOTAL <Country,MYear> Country)

3. IF(Country<>'Other',Count({<Profile={'O'},MYear={'$(=MonthName(max(MYear)))'}>}Country))/

Count({<MYear={'$(=MonthName(max(MYear)))'}>} TOTAL <Country,MYear> Country)

For TOTAL I use Dimension Limits tab , Options=>Show Total.

Regards,

Help with expression

Thu, 23 Feb 2012 18:31:02 GMT

OK So if the Brand is equal to A or B you want to just see the dimension "Total" and if it is not then see the specific country?

If that is the case you could try:

IF(Brand<>'A' and Brand<>'B' and (len(trim(Var1))>0, Country, 'TOTAL')

Help with expression

Anonymous — Thu, 23 Feb 2012 18:48:39 GMT

Yes, That's exactly that I need.

=IF(qID_2729_Brand<>'A' and(Len(Trim([Profile]))>0),Country,'TOTAL')

Could you help me to create an expression which show Country and TOTAL for all Brands except Brand=A?

My Country:

If([qID_6317_In which of the following countries do you reside?]='Canada','US/Canada',

If([qID_6317_In which of the following countries do you reside?]='United States','US/Canada',

If([qID_6317_In which of the following countries do you reside?]='United Kingdom','United Kingdom',

If([qID_6317_In which of the following countries do you reside?]='Germany','Germany',

If(LEN(TRIM([qID_6317_In which of the following countries do you reside?]))>0,'Other')))))as Country,

Re: Help with expression

Thu, 23 Feb 2012 18:55:37 GMT

If the field Brand resides in the same table as [qID_6317_In which of the following countries do you reside?] then it is easy. This will show total if Brand=A and Country if it does not:

if(qID_2729_Brand='A','Total',

If([qID_6317_In which of the following countries do you reside?]='Canada','US/Canada',

If([qID_6317_In which of the following countries do you reside?]='United States','US/Canada',

If([qID_6317_In which of the following countries do you reside?]='United Kingdom','United Kingdom',

If([qID_6317_In which of the following countries do you reside?]='Germany','Germany',

If(LEN(TRIM([qID_6317_In which of the following countries do you reside?]))>0,'Other')))))) as Country,

Re: Help with expression

Anonymous — Thu, 23 Feb 2012 18:59:32 GMT

Should I use it, when I load my data?

I need to show only TOTAL just for 3 brands of 10.

Re: Help with expression

Anonymous — Thu, 23 Feb 2012 19:03:56 GMT

It should look like this for all brands. For Brand =A it should be only TOTAL

Re: Help with expression

Thu, 23 Feb 2012 19:06:22 GMT

What are the ending results that you want to see?

The above formula is for the script if Brand is in the same table as the country field. If it is not then you can change the dimension to the formula and that should also work.

The above formula will return:

First.

If Brand=A, 'Total'

If Brand<>A,

US/Canada,

United Kingdom,

Germany,

Other

This formula only works if Brand is in the same table.

If a country has more than one brand it could be split into different categories. IE:

If your data looks like this:

United States A

United States B

United States C

then United States with brand A will be put into the Total group and United States with brand B and C will be put into US/Canada

Re: Help with expression

Anonymous — Thu, 23 Feb 2012 19:14:48 GMT

Yes, they are in one table.

When I load my data, I create a new variable Country.(US/Canada, UK, Germany and Other(all others countries)).

This expression works

=IF(qID_2729_Brand<>'A' and qID_2729_Brand<>'B' and qID_2729_Brand<>'C' and(Len(Trim([Profile]))>0),Country,'TOTAL')

It shows only TOTAL for these brands.

Is it possible to show country and TOTAL together for other brands.

I use Dimension Limits for that. I`d like remove it and use your TOTAL.

Re: Help with expression

Thu, 23 Feb 2012 19:25:17 GMT

So you want to see Country, Total, and Total of Country + Total?

Re: Help with expression

Anonymous — Thu, 23 Feb 2012 19:34:33 GMT

Ther`s no way to show Country variable and 'TOTAL' in one chart???

Re: Help with expression

Anonymous — Thu, 23 Feb 2012 19:41:04 GMT

I want to see Country and TOTAL.

Please see my image below:

Instead of Global use your 'TOTAL'.