47 Replies Latest reply: Feb 5, 2015 11:36 AM by Emmanuel Prieto

# Multiplying two fields in a expression!

Hi

Imagine if Date is there are as a dimension in line chart and I want some to have some below in the expression. Is it possible?

`Sales * Count(ID) //Where Sales and ID are the fields.`

I tried something same like above but it is not giving any value. Is there is anyother way of doing it please.

Hope some can help me out.

• ###### Multiplying two fields in a expression!

Hi,

Please explain ur problem in details. What are the dimenssions, we can use the aggr function also.

Can u post some sample application?

Thanks

• ###### Multiplying two fields in a expression!

`Manesh wrote:Please explain ur problem in details. What are the dimenssions, we can use the aggr function also.Can u post some sample application?<div></div>`

Thanks!

Dimension is a Date. I think if you look into the below reply you should get to know what I am looking for. If not please let me know.

• ###### Multiplying two fields in a expression!

i think you are missing SUM function. try : Sum(Sales)*Count(ID)

Regards, tresesco

• ###### Multiplying two fields in a expression!

HI,

Or use Sum(Sales*aggr(Count(ID),Dimenssion))

• ###### Multiplying two fields in a expression!

`tresesco wrote:i think you are missing SUM function. try : Sum(Sales)*Count(ID)`

I have already tried that but it giving in correct result.

If try that as per your suggestion, where Sales=306 and ID=1244.

There result should be 306 * 1244 = 380664 but where as if I try as per your suggestion it is giving 14845896.

• ###### Multiplying two fields in a expression!

Hi,

Please check the frequency of Sales.

send me ur applicatio (Sample).

• ###### Multiplying two fields in a expression!

`Manesh wrote:Please check the frequency of Sales.send me ur applicatio (Sample). <div></div>`

Above sugggestion also didn't work. I am sorry I cannot attach the sample application. I think you can use your own data that you have to work on it.

• ###### Multiplying two fields in a expression!

hi Attitue,

I think it should be Sum(Sales) * count(distinct ID)

• ###### Multiplying two fields in a expression!

`Deepak Kurup wrote:I think it should be Sum(Sales) * count(distinct ID)<div></div>`

Thanks but it didn't work :-(

• ###### Multiplying two fields in a expression!

hi Attitude,

It did work ?? or didnt work ??.. if it didnt work.. Then create two separate expression and check the values. and in third expression add column(1) * Column(2).

I think there might be an issue in the data model,

• ###### Multiplying two fields in a expression!

pls Check whether link is perfect between this field.

can you post application with limited data ?.

• ###### Multiplying two fields in a expression!

Are you trying : Sum(Sales) * Count (ID) (count would count the occurance/ frequency of ID, it would not give you ID value) OR,

Sum(Sales*ID) OR Sum(Sales) * ID All are very much different. it depends on your data and your requirement. if it is getting you confused, please upload your sample application as suggested by Manesh.

Regards, tresesco

• ###### Multiplying two fields in a expression!

Have attached the sample now. Please check and let me know some solution.

• ###### Multiplying two fields in a expression!

Dear

• ###### Multiplying two fields in a expression!

`Sunil Jain wrote:Pls check and Reply<div></div>`

Give me some time. I will check and get back to you

• ###### Multiplying two fields in a expression!

Why there is no data in the chart? Can you please check and let me know

• ###### Multiplying two fields in a expression!

hi Attitude,

Just need a clarification. I goan through your data and I was finding it diffcult to understand the data.

you data looks like.

Field1 Field2 ID Year Month Sales

A X 1 2009 X3 6

A X 1 2009 S3 6

Now in the above case if you look the month is getting repeated twice.

As per you output you require only Sales as 6 for which you are writing in Expression Sales

and if we make Sum(Sales) the value becomes 12 which you said is wrong.

There is something wrong either in the data or in the way we are analyzing.

• ###### Multiplying two fields in a expression!

Hi,

I think u r not explaining the problem / situation correctly. This is not a big issue.

Then also u can try

=

sum(Distinct SALES)*Count(ID)

or

=

aggr(sum(Distinct SALES),ID)*Count(ID)

And revrt back , we are curious to see the solution.

• ###### Multiplying two fields in a expression!

Hi Manesh

Got stuck with some issues. Thats why I couldn't respond to you properly. Will get back to you very soon.

• ###### Multiplying two fields in a expression!

Hi,

• ###### Multiplying two fields in a expression!

Hi Manesh

Thanks! Thats a Great Job! You have shown that it is possible to achieve that in many other ways.

• ###### Multiplying two fields in a expression!

Hi Attitude,

My trick is useful to u or not?

• ###### Multiplying two fields in a expression!

Thanks Manesh! Yes your tip has helped me!

By the way I have one issue which is more or less exactly the same way like this. Hope you can help me out there as well.

Below expression I use in on my chart. What I want is I want to use "count(ID) * SALES" instead of "ID". But id didn't work! Do you have any idea about it please.

`=count(distinct(if(len(DATE)>0,ID)))`

• ###### Multiplying two fields in a expression!

Hi,

Please try this. and let me inform its working or not.

=count(distinct(if(len(trim(DATE))>0,ID)))

• ###### Multiplying two fields in a expression!

Thanks Manesh

Thats ok! But my question was you need to pass "count(ID) * SALES" instead of "ID". That is very important here!

• ###### Multiplying two fields in a expression!

I want to do something like below. Hope it is understandable now!

`=count(distinct(if(len(DATE)>0,count(ID) * SALES)))`

• ###### Multiplying two fields in a expression!

Hi all,

Plz help me on this,

i've a table

X            Persons

-----------------------------------

0            A

0.5         B

0.5         C

1            D

1            E

This means no. of Persons in Category 0: 1, in category 0.5 : 2 & in category 1: 2

So, i want a Qlikview table showing X as a dimension & expressions as No. of persons in each(which can be simply done by count(Persons)). The problem is the third col which should show the product of these two

X  Count(Persons)            Multiply

-----------------------------------------------

0          1                            0

0.5       2                            1

1         2                            2

• ###### Re: Multiplying two fields in a expression!

Hi,

You can create a straight table with dimension as X and first expression as Count(Persons) and label this as Count

Add another expression as X * Count

Also find the sample application attached.

Regards,

Kaushik Solanki

• ###### Multiplying two fields in a expression!

Hi,

Use = X * Count(Persons) as Expression

Or

If your using Count of Persons as Expression not a dimension then u can use this also

= X * Column(1)

Hope this is you need

Celambarasan

• ###### Multiplying two fields in a expression!

Thank you both Kaushik & Celambarasan.

But actually the problem is not as simple as i've made it look.

Actually i've two more dimensions D  & E ,the actual table is like below:

D          E           X

-------------------------------------------

a          p           0

a          q           0.5

a          r            1

b          p            1

b          r           1

c          p          0.5

d          q           0.5

Now i want a pivot table with d as 1st dimension,E as 2nd dimension with expr1 as count in each E(no of persons which is simple & the 2nd expr as sum(no. of persons in each E multiply by X)

I think aggr shud be for 2nd expr: sum(aggr(count(Name),X))............but i'm committing a istake somewhere.

Plz help

• ###### Re: Multiplying two fields in a expression!

Hi,

Have a look at the example attched.

Regards,

Kaushik Solanki

• ###### Multiplying two fields in a expression!

Thanks again,

But actually Kaushik i u just replace the q in the 2nd row in the 2nd col. of the inline table by p, which is the actual scenario

D, E, X
a, p, 0
a, p, 0.5
a, r, 1
b, p, 1
b, r, 1
c, p, 0.5
d, q, 0.5
]

;

then the we have 2 persons in E, from which 1 is having 0 & other 0.5,so although the 1st expr shud show 2 the 2nd shud show (1*0+1*0.5)=0.5 which is not the case.

Finally i've reached to the complete problem.

• ###### Re: Multiplying two fields in a expression!

Hi,

Have a look at the attched example.

Regards,

Kaushik Solanki

• ###### Re: Multiplying two fields in a expression!

Hi, Thanks again for the reply

Actually i want the expr to be X* count(distinct E) and when i add X also as 3rd dim. & set show partial sums for each dim....then althogh sum of count(E) is correct, sum of x*count(distinct E) is null only for 1st row interestingly

plz help once more

• ###### Re: Multiplying two fields in a expression!

Hi,

I am not getting, can you explain with example.

Regards,

Kaushik Solanki

• ###### Re: Multiplying two fields in a expression!

i've modified ur qlikview file.....dnt know how to attach it to msg.

plz help me in this......

• ###### Re: Multiplying two fields in a expression!

any luck Kaushik ??

• ###### Re: Multiplying two fields in a expression!

Hi,

This problem is because you have choose the Expression total option in the Properties-->Expression tab-->Unter the total mode you have 3 options.

Select sum of rows option to solve your problem.

Celambarasan

• ###### Re: Multiplying two fields in a expression!

Hi,

Try this as expression in your pivot table.

sum(aggr(sum(X),X,D,E) * aggr(count(distinct E),X,E,D))

Regards,

Kaushik Solanki

• ###### Re: Multiplying two fields in a expression!

Thanks Kaushik.............i dnt have words to thank you

hats off to you buddy

keep in touch

• ###### Re: Multiplying two fields in a expression!

Hi,

My pleasure.

Regards,

Kaushik Solanki

• ###### Re: Multiplying two fields in a expression!

One more query Kaushik, i've a master table having say departments & transaction table,now i want to set up incremental update on the transaction table having some timestamp as a pkey, same named timesatmp key also exist in the master tabe dept. currently i'm taking data from the qvds of the two tables. Now if in future some dept has to be added in the master then how to account all this in the incre. load

Plz help

• ###### Re: Multiplying two fields in a expression!

Hi,

I didnt get your requirement clearly..

Please open a new discussion and explain me their with example.

Regards,

Kaushik Solanki

• ###### Report Format

Hi Kaushik,

There's another one for you Sir, i need to create a table in the format attached.

The dim 2 has 3 categories Dim2(a),Dim2(b),Dim2(c) & The Dim3 has 3 categories one of wich is not used in this report(leave it, call it Dim3(c)), 1st is Dim3(a), for wich we have expr 1 & expr 2(calculalted only for Dim3(a)) & 2nd is Dim3(b) for wich again exprr1 & expr2 are to be calculated.

The problem is

1) the format of the report wich has dim 2 & dim 3 at the same level of pivot table whne the dim is dragged & dropped upwards so as to resemble this

2)  calclation of expr2 for if (Dim3=Dim3(a)) & for Dim3=Dim(b)

u can also call Dim3(a),Dim3(b) etc. as x,y,z

• ###### Re: Multiplying two fields in a expression!

Hello Sir,

I've a new problem of calculating differences of two cols based on some condition.

I've two tables: 1st  having dimensions as Dim 1--> Dim 2-->Dim 3.....they've hierarchial relation as we represent in a pivot table.

But here we're concerned only wid Dim 3….the expr is Exp 1…Table1.xlsx is attached

Now the other is an excel file in crosstable format having sheets named 2011 & 2012 (Years) having

Cols Dim 3 & expr2…Table2.xlsx is attached….the script is below

Dim3,

January,

February,

March,

April,

May,

June,

July,

August,

September,

October,

November,

December

FROM '\$(file)' (ooxml, embedded labels,table is \$(sheetname));

I’m fetching them in QV & need to calculate the third expression : expr3 as

Expr3=if(expr1-expr2 for feb 2012>0,30 (expr1/expr2)*30)

Calculate Expr4 if  Expr3=30: Expr4= if(expr1-(expr2 for feb 2012 + expr2 for Jan2012)>0,30,(expr1-expr2 for Feb2012)/(expr2 for Jan 2012)*30)

Calculate Expr5 if Expr4=30: Expr5=if (expr1-(expr2 for feb 2012+expr2 for Jan2012 +expr2 for Dec2011)>0,30,(expr1-( expr2 for feb 2012+expr2 for Jan2012))/(expr2 for Dec 2011)*30

And so on……till expr3/expr4/expr5 is less than 30...and as soon as we get any of them less than 30 then add another col showing the sum:

for eg….expr3=30,expr4=30 and expr5=29, so Sum col=expr3+expr4+expr5

where & how  shud this logic be put……in script or in GUI expressions……

Plz help

Thanks & Regards

• ###### Multiplying two fields in a expression!

Hi Manesh

Have you got any idea on achieving the same? If yes please share it with me.

• ###### Re: Multiplying two fields in a expression!

Hello At!

have you tried the basic?

If you ar looking for this:

"If try that as per your suggestion, where Sales=306 and ID=1244.

There result should be 306 * 1244 = 380664 but where as if I try as per your suggestion it is giving 14845896."

306 * 1244 = 380664  then use SUM(Sales*ID)

Hope this is what you are looking for!

Regards