Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
Hi
Try adding [ ] around Transport Costs:
Sum({$<CatCode={1},[Transport Costs]={'Normal Freight'}>}Amount)-Sum({$<CatCode={2},[Transport Costs]={'Normal Freight'}>}Amount)
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.
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
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
hei
attach is an example
try this
Hi,
I have tried the 1st stmt and it doesn't work... 2nd stmt is syntactically not correct..
Thanks
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
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
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)