topic How to Combine 2 Expressions into One Expression in QlikView

How to Combine 2 Expressions into One Expression

Thu, 18 Aug 2011 06:22:39 GMT

Hi Guys / QlikCommunity:

I have this Expression:-

=sum(if(transdate<CurrentDate -2,Qty))

And also this Expression:-

sum (if (invtype='20',goodqty)) - sum (if (invtype='34',goodqty))

sum (if (invtype='40',goodqty)) - sum (if (invtype='42',goodqty))

I need to combine both Expressions into one Expression.

I am not able to use Set Analysis for my Expression.

Could someone enlighten me how to do it?

Thanks a lot guys…

How to Combine 2 Expressions into One Expression

lironbaram — Thu, 18 Aug 2011 06:25:17 GMT

hei

can you explain a little bit more what you mean by combine the expression

what the resault you are after?

How to Combine 2 Expressions into One Expression

Thu, 18 Aug 2011 06:33:04 GMT

Sorry guys:

Forget to add this:-

The Qty in the first Expression is equal to the second Expression which is

sum (if (invtype='20',goodqty)) - sum (if (invtype='34',goodqty))

sum (if (invtype='40',goodqty)) - sum (if (invtype='42',goodqty))

I need to combine both the Expression.

Thanks Guys

How to Combine 2 Expressions into One Expression

gandalfgray — Thu, 18 Aug 2011 06:37:55 GMT

How about:

sum(if(transdate<CurrentDate -2,

if (invtype='20',goodqty) - if (invtype='34',goodqty)

if (invtype='40',goodqty) - if (invtype='42',goodqty))

))

How to Combine 2 Expressions into One Expression

lironbaram — Thu, 18 Aug 2011 06:41:17 GMT

lets give it a try

the first one is assuming currentDate is Varaible with the same value to all data

sum ({<invtype={20,40},transdate={"<$(=(#CurrentDate -2))"}>}goodqty)-

sum ({<invtype={34,42},transdate={"<$(=(#CurrentDate -2))"}>}goodqty)

the second for the case CurrentDate is a field with more then one value

sum ({<invtype={20,40}>}if(transdate<CurrentDate -2,goodqty))-

sum ({<invtype={34,42}>}if(transdate<CurrentDate -2,goodqty))

hope its helps you

How to Combine 2 Expressions into One Expression

Fri, 19 Aug 2011 06:24:03 GMT

Hi GandalfGray and Guys:

Thanks you for your reply.

I try your Expression

sum(if(transdate<CurrentDate -2,

if (invtype='20',goodqty) - if (invtype='34',goodqty)

if (invtype='40',goodqty) - if (invtype='42',goodqty))

))

The result is Zero.

No sure what happen.

Hi liron baram:

I am not able to try your Expression because I am not able to use Set Analysis as I am using Qlikview 7.5.

There must be something wrong somewhere that I just can not pin point it…

Could anyone please help?

Thanks a lot

Re: How to Combine 2 Expressions into One Expression

Anonymous — Fri, 19 Aug 2011 06:55:05 GMT

not sure if this is wat you want.. but you can try this...

    if(transdate < (CurrentDate -2), sum(Qty),
    (if (invtype='20',sum(goodqty)) - if (invtype='34',sum(goodqty))) +
    (if (invtype='40',sum(goodqty)) - if (invtype='42',sum(goodqty)))
    )

hope this helps...

How to Combine 2 Expressions into One Expression

gandalfgray — Fri, 19 Aug 2011 07:09:33 GMT

Hi richardgan

I suggest you build the statement step by step and checks what part that makes you "lose" your expected data.

Starting with:

sum(if(transdate<CurrentDate -2, goodqty))

Make invtype a dimension and check what you get for all invtypes

If you get zero for all invtypes from that but are expecting something else you got to find why.

How to Combine 2 Expressions into One Expression

Fri, 19 Aug 2011 08:32:37 GMT

Hi GandalfGray:

Thanks for your reply I will try just that.

Hi aadilmadarveet:

May i know where the Qty come from?

My First Expression:

=sum(if(transdate<CurrentDate -2,Qty))

The first Expression I have is that I need to change the Qty to the following Expression.

sum (if (invtype='20',goodqty)) - sum (if (invtype='34',goodqty))

sum (if (invtype='40',goodqty)) - sum (if (invtype='42',goodqty))

That is where I am trying to solve.

Thank you all for your kind help.

How to Combine 2 Expressions into One Expression

Anonymous — Mon, 22 Aug 2011 07:39:09 GMT

Hi Richard,

I Think you can try this..

You can create a variable for CurrentDate -2 and then use it in the expression...

Sum({<transdate={"<$(DateVariable)"}>} (

(sum (if (invtype='20',goodqty)) - sum (if (invtype='34',goodqty)))

(sum (if (invtype='40',goodqty)) - sum (if (invtype='42',goodqty)))

))

hope i understand your question and providing some valuable solution..

thanks,

aadil

How to Combine 2 Expressions into One Expression

gandalfgray — Mon, 22 Aug 2011 07:51:04 GMT

richardgan is using 7.5 and can't use set expr because of that...

How to Combine 2 Expressions into One Expression

Mon, 22 Aug 2011 08:24:51 GMT

Hi Guys and aadilmadarveet:

GandalfGray is right I am not able to use Set Analysis as I am using Qlikview 7.5 and it does not support Set Analysis for that.

I think I will need to change the Expression as it takes very long time to long and the data looks funny.

Once I have the answer to that I will post my scrip and Expression in this post so that others can have a look.

Thank you guys for the kind and great help.

How to Combine 2 Expressions into One Expression

Mon, 22 Aug 2011 08:36:19 GMT

Guys thanks you so much for your kind help.

I decided to check the Expression to the following and it works BUT there is one Big Problem.

(

(Sum (if(year(InventoryDate1)<=(year(varcurrdate))

and Inventorymonth1<month(varcurrdate)

and (InventoryDate1< varcurrdate)

and (invtype='34'),InventoryGoodqty)))

(Sum (if(year(InventoryDate1)<=(year(varcurrdate))

and Inventorymonth1<month(varcurrdate)

and (InventoryDate1< varcurrdate)

and (invtype='20'),InventoryGoodqty)))

)

(

(Sum (if((year(InventoryDate1)<=year(varcurrdate))

and (invtype='40') and locid='0000'

,InventoryGoodqty)))

(Sum (if((year(InventoryDate1)<=year(varcurrdate))

)

and (invtype='42') and locid='0000'

,InventoryGoodqty)))

)

After checking the data. The Opening Balance should be the Closing month balance for the pervious month. The Above Expression is the Opening Balance.

The Closing Balance is as Follows:-

[Opening Bal] + [MTD Variance]

I am trying to include the Expression for Closing Balance as the Opening Balance for the following month.

Any kind soul, please enlighten me.

Thanks guys for your wonderful and helpful feedback.