DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

[#1]

Hi Everybody,

I have a Pivot table and want to to create 2 expression for one row in 2 columns: These are my calculation:

=SUM({<GRP4_DESC_F={'M.O.D Directe Et Indirect'}>}BALANCE)

=SUM({<GRP4_DESC_F={'M.O.D Directe Et Indirect'}>}TONS)

As I said I want :

I have to do it by expression bcuz  some items from "GRP4_DESC_F" should be selected in certain order.

Any idea is appreciated.

Thanks,

Nima

Header 1	01/01/2011	01/01/2011
	BALANCE	TONS
'M.O.D Directe Et Indirect'	200000	450

[SunilChauhan]

use a pivot or straight taable

write two expresssion using same formula you have mentioned.

and in dimension->add expression-> write 'M.O.D Directe Et Indirect'

will get desired result

[#3]

Thanks Sunil,

The problem is I have several rows different Items from different field that's why I can't do it by dimension and it has to be done by expression. for example first row is the row I mentioned above next row is "Avantages Sociaux - Horaire " , 3th row is "Majoration Des Heures Suppl." and so on .......

Hope I explained it well.

[Anonymous]

Hi Nima,

I'm not sure to get everything, but if you want 2 columns, you have to create 2 expression on the expression tab.

In expression tab -> right clic and add a new expression.

You can create a lot of differents expression for one dimension like this.

hope that can help.

Regards,

Arnaud

[#5]

Thanks Adurquety,

Here is the case : I have a field name : GRP4_DESC_F .

I have to show some of the rows of this field like : 'M.O.D Directe Et Indirect' , "Majoration Des Heures Suppl. ", "Avantages Sociaux - Horaire " and so on. and I might have more fields and I have to pick some of the rows of this field in certain order  which are not the same order as I have in my table.  I have DATE on the top column. for example on the row I want to see 'M.O.D Directe Et Indirect' for the corresponding column 1 I want toshow  BALANCE , column 2  TONS.

and so on for the rest . I can not use dimension  GRP4_DESC_F.  Bcuz can't put them in desired order and also can't add some of them together. That's why I have to use expression as I have here:

=SUM({<GRP4_DESC_F={'M.O.D Directe Et Indirect'}>}BALANCE)

=SUM({<GRP4_DESC_F={'M.O.D Directe Et Indirect'}>}TONS)

I hope I explained it clearly.

Thanks,

Nima

[#6]

You can use the valuelist function.

1.  Create a dimension and use the expression "valuelist('M.O.D Directe Et Indirect','Majoration Des Heures Suppl. ','Avantages Sociaux - Horaire')"  Of course you can add any I missed.  This list can also be generated from a list of fields using some concatenate mechanics, but we can get into that once you get the base working.

2.  In your expression, you will need to use an IF statement to generate the sums like this.  "SUM(IF(GRP4_DESC_F = valuelist('M.O.D Directe Et Indirect','Majoration Des Heures Suppl. ','Avantages Sociaux - Horaire'), BALANCE))

The valuelist works as a synthetic dimension and then you simply use it as your IF argument.

[#7]

ThanksAwitt,

That could be helpful when you just have one set. Bcuz I want to have some calculation after I have them. Like :

'M.O.D Directe Et Indirect' + 'Majoration Des Heures Suppl. '

,'Avantages Sociaux - Horaire'  -  'Majoration Des Heures Suppl. '

Which I think is not possible !!!!

Thanks,

Nima

[#8]

Can you give an example of the output you want?

[#9]

Here you are the example:

Header 1	01/01/2011	01/01/2011
	BALANCE	TONS
'M.O.D Directe Et Indirect'	4000	2000
'Majoration Des Heures Suppl. '	1500	1000
'M.O.D Directe Et Indirect' + 'Majoration Des Heures Suppl. '	5500	30000

Thanks,

[#10]

In this exact example, you could turn on expression totals and have it return a total of the values.  However, based on your previous examples, I think you want some other added and subtracted subtotals such as "'Avantages Sociaux - Horaire'  -  'Majoration Des Heures Suppl. '".  Unfortunately, without any established structure to your dimensions, there isn't anyway to do this.  Qlikview has no way of knowing where the breaks need to occur.