topic Re: Need help with this set statement!! in QlikView

Need help with this set statement!!

Fri, 05 Aug 2011 12:44:58 GMT

Hello,

I have a pretty simple table structure as shown below and I am trying to create a bar chart which would show the Amount difference.

Table1

CatCode

Region

CostType

ProductGroup

LocationCode

Month

Amount

Expr which I am using is : Sum({$<CatCode={1},Transport Costs={'Normal Freight'}>}Amount)-Sum({$<CatCode={2},Transport Costs={'Normal Freight'}>}Amount)

My cat code is nothing but 1 for Budget and 2 for Actual.

However, I cannot get this working. Any help is greatly appreciated.

Thanks

Need help with this set statement!!

gandalfgray — Fri, 05 Aug 2011 12:53:07 GMT

Try adding [ ] around Transport Costs:

Sum({$<CatCode={1},[Transport Costs]={'Normal Freight'}>}Amount)-Sum({$<CatCode={2},[Transport Costs]={'Normal Freight'}>}Amount)

Re: Need help with this set statement!!

Fri, 05 Aug 2011 12:58:21 GMT

Hi, I had done that. TransportCosts is how I have it in my above table, and the expression is

Sum({$<CatCode={1},TransportCosts={'Normal Freight'}>}Amount)-Sum({$<CatCode={2},TransportCosts={'Normal Freight'}>}Amount)

still does not work.. even if I remove TransportCosts I do not get the sum by CatCode as well.

Re: Need help with this set statement!!

Fri, 05 Aug 2011 17:30:29 GMT

First I am sorry if I am mistaken,

I can't see [Transport Costs] field in your table.

Second, if this field exists than I hope GandalfGray has correctly answered.

And also put CatCode={'1'}, I mean put quotes around the CatCodes values 1 & 2

Third, Please upload a test application with sample data so that we can try it out for you.

Regards,

Bikash Debnath

Need help with this set statement!!

Mon, 08 Aug 2011 07:12:22 GMT

Hi arch25,

the expression which you are using isnt the correct format for set analysis

Try using the following code

sum({<CatCode={"1"},TransportCosts)={"Normal Freight"}>}Amount) -sum( {<CatCode={"2"},TransportCosts)={"Normal Freight"}>}Amount)

if that doesnt't work( for some reason, though i can't see y it wudn't,

try this too

sum({<$(CatCode)={"1"},$(TransportCosts)={"Normal Freight"}>} Amount) -sum( {<$(CatCode(={"2"},$(TransportCosts)={"Normal Freight"}>}Amount)

Rgrds,

Abhinava

Re: Need help with this set statement!!

lironbaram — Mon, 08 Aug 2011 08:09:29 GMT

hei

attach is an example

try this

Re: Need help with this set statement!!

Mon, 08 Aug 2011 08:44:14 GMT

Hi,

I have tried the 1st stmt and it doesn't work... 2nd stmt is syntactically not correct..

Thanks

Re: Need help with this set statement!!

Mon, 08 Aug 2011 09:18:03 GMT

Hi arch25,

you were correct on the 2nd statement being syntactically wrong... but the 1st stmt is correct..

looking at liron example qvw, everything looks fine and working.

i would advise you to reevaluate your linking, the transportcosts field should somehow be linked to the table containing the catcode, amount.......etc

Rgrds,

Abhinava

Re: Need help with this set statement!!

Mon, 08 Aug 2011 09:28:11 GMT

Hi arch25,

checking out ur example... the chart is not displayed because the sum(Amount) value for both catcode=1 and 2 are same...

that y it is blank... though it can be made to disply the axis nevertheless...

also u need to select a dimension , either product hrp or month or watever u want..

goto presentation tab in the chart propeteies and uncheck the "suppress zero values" and "suppress missing values" check boxes, you'l see the graph axis then,, ofcourse there'l be no bars coz the values cancel out.

rgrds,

Abhinava

Need help with this set statement!!

SunilChauhan — Mon, 08 Aug 2011 10:19:24 GMT

value of catCode =1 and catcode=2 is same

i believe u need to use + sign in betweeen two sum values like below

sum({<CatCode={"1"},TransportCosts={"Normal Freight"}>}Amount) +

sum({<CatCode={"1"},TransportCosts={"Normal Freight"}>}Amount)