topic Re: Need a help in if condition query.... in QlikView

Need a help in if condition query....

Wed, 28 Nov 2012 07:46:20 GMT

Dear All,

I have a issue on below conditon .......

= sum (if(id=5,value) * if(id=10,value))

id = 5 is Qty

id = 10 is Rate

The above condition is showing zero....

Can any one help me...

Regards,

Antony.

Re: Need a help in if condition query....

sivarajs — Wed, 28 Nov 2012 07:51:48 GMT

sum (if(id=5,value,if(id=10,value)))

Re: Need a help in if condition query....

Wed, 28 Nov 2012 08:02:24 GMT

Hi Sivaraj,

Thanks for your reply.

You conditon is adding both the values. but i want to multiply both the values.

Thanks,

Antony.

Re: Need a help in if condition query....

its_anandrjs — Wed, 28 Nov 2012 08:06:07 GMT

Try this

= sum (if(id=5,value) ) * sum( if(id=10,value))

Regards,

Anand

Re: Need a help in if condition query....

Wed, 28 Nov 2012 08:10:28 GMT

Dear Aanad,

Thanks for your reply.

Your condition will not show the total value correctly due to sum of Rate. if i do the sum of rate and sum of Qty separately my total won't match. that is why im doing both the if condtion in one sum function.

Regards,

Napolean.

Re: Need a help in if condition query....

its_anandrjs — Wed, 28 Nov 2012 08:13:39 GMT

Can you provide any sample file for this or data file or sample data.

Regards,

Anand

Re: Need a help in if condition query....

Anonymous — Wed, 28 Nov 2012 08:19:59 GMT

Hi..

num(sum(if(id=5, value))) * num(sum(if(id=10,value)))

regards,

Akbar

Re: Need a help in if condition query....

Wed, 28 Nov 2012 08:43:38 GMT

Dear All,

Please find below the eg.

Product code	Id	Value
1	10	5
2	10	10
3	10	15
4	10	20
5	10	25
1	5	5
2	5	4
3	5	3
4	5	7
5	5	8

Sales = Rate (10)* Qty (5)

Sales Value i need 450

Regards,

Antony

Re: Need a help in if condition query....

Anonymous — Wed, 28 Nov 2012 08:56:10 GMT

Hi..

Let's try this :

sum(if(id=10, value)) * sum(if(id=5, value))

Regards,

Akbar

Re: Need a help in if condition query....

its_anandrjs — Wed, 28 Nov 2012 08:59:17 GMT

Sales = Rate (10)* Qty (5)

This columns Rate and Qty not in your eg please explain it.

Regards,

Anand

Re: Need a help in if condition query....

Wed, 28 Nov 2012 09:14:27 GMT

Dear Anand,

ID = 10 is Rate and ID = 5 is Qty.

Re: Need a help in if condition query....

Wed, 28 Nov 2012 09:16:11 GMT

Dear Akbar,

i tried this, it's showing more than my actual output.

Pls see the eg. which i showed on above.

Regards,

Antony.

Re: Need a help in if condition query....

Anonymous — Wed, 28 Nov 2012 09:18:51 GMT

Hi,

You might be having NULL record in the "value" column. Convert it to 0.

Below expression may work,

=sum (if(id=5,if(isnull(value), 0, value)) * if(id=10,if(isnull(value), 0, value)))

Thx

Satya

Re: Need a help in if condition query....

Anonymous — Wed, 28 Nov 2012 09:21:46 GMT

Antony Napolean,

In a multiplication let us say e.g. A * B. If any of A or B is zero then the product will be zero.

Your condition will always be zero : sum (if(id=5,value) * if(id=10,value)). Is it possible a single line have two values for the field id. The answer is not.

A simple solution is break the table into two tables.

Prod_code Rate_Id Rate

1 10 5

2 10 10

3 10 15

4 10 20

5 10 25

Prod_code Qty_ID Qty

1 5 5

2 5 4

3 5 3

4 5 7

5 5 8

Now the required formula is = sum (Rate * Qty)

Hope this helps!

Regards

MultiView

Re: Need a help in if condition query....

Wed, 28 Nov 2012 09:22:28 GMT

Dear Satya,

Your expression is showing zero on my application.

Re: Need a help in if condition query....

sujeetsingh — Wed, 28 Nov 2012 09:34:31 GMT

Try this

(sum(if(ProductID='A',Number))) * (sum(if(ProductID='D',Number)))

if not doing just post a sample

Thanks.

Re: Need a help in if condition query....

Wed, 28 Nov 2012 09:36:19 GMT

I already posted a sample. Pls see above.

Re: Need a help in if condition query....

jagannalla — Wed, 28 Nov 2012 09:40:04 GMT

Hello Antony,

Still I'm confuse in your query and explaination. What is the dimension you are consider in the charts ? Because the expression what you are trying to develp is wrong. How can you consider two conditions at a time for a single column in expression.

If condition works record by record of column. We can't develop two conditions to work as single point. Please let us know what is the output you want to see based on input you mention.

In your condition you are trying to get sum of value where Id=10 it is fine. But at same level you are trying to get sum of value where Id=5.

You can go with set analysis.. If you take ProductCode as dimension and expression as

Sum({<Id={10}>}Value)* Sum({<Id={5}>}Value)

Cheers!!

Jagan

Re: Need a help in if condition query....

Wed, 28 Nov 2012 09:49:03 GMT

Hi Jagan,

Pls find below my eg.

Product code	Id	Value
1	10	5
2	10	10
3	10	15
4	10	20
5	10	25
1	5	5
2	5	4
3	5	3
4	5	7
5	5	8

My output is 450.

I want to show the total sales.

for my sales formula is Sales = (Qty * Rate). i have qty and rate both values in one field. so i need to separate the qty and rate by using id 5 and 10.

I need the above output 450 , how will i get ?.

Regards,

Antony

Re: Need a help in if condition query....

jagannalla — Wed, 28 Nov 2012 09:54:13 GMT

You can go with set analysis.. If you take straight table ProductCode as dimension and expression as

Sum({<Id={10}>}Value)* Sum({<Id={5}>}Value).

Now in Expression tab of ST -> Total Mode as Expression Total. Now you can see total count in top of expression.

Cheers!!

Jagan