Need help with this set statement!!

[#1]

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

[gandalfgray]

Hi

Try adding [ ] around Transport Costs:

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

[#3]

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.

[#4]

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

[#5 · Accepted solution]

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

[lironbaram]

hei

attach is an example

try this

[#7]

Hi,

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

Thanks

[#8]

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

[#9]

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

[SunilChauhan]

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)