topic DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS) in QlikView

DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

Tue, 23 Aug 2011 15:36:15 GMT

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

DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

SunilChauhan — Tue, 23 Aug 2011 15:48:56 GMT

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

DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

Tue, 23 Aug 2011 15:55:02 GMT

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.

DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

Anonymous — Tue, 23 Aug 2011 16:35:25 GMT

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

DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

Tue, 23 Aug 2011 17:14:25 GMT

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

DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

Tue, 23 Aug 2011 17:28:15 GMT

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.

DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

Tue, 23 Aug 2011 17:51:42 GMT

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

DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

Tue, 23 Aug 2011 19:09:52 GMT

Can you give an example of the output you want?

DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

Tue, 23 Aug 2011 19:21:25 GMT

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,

DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

Tue, 23 Aug 2011 19:29:07 GMT

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.

DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

Tue, 23 Aug 2011 19:34:43 GMT

You are right, I tried almost every thing and could not find a solution. We are trying to switch our cognos reports to Qlick but unfortunately Qlick is not able to do every thing that is possible in cognos reporting tools.

Thanks,

Nima

DOUBLE EXPRESSION IN ONE ROW (FOR 2 COLUMNS)

Fri, 26 Aug 2011 17:44:28 GMT

I'm still looking for a solution!

Any suggestion is appreciated ,

Thanks