Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

Need help with this set statement!!


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

Tags (2)
1 Solution

Accepted Solutions
Not applicable

Need help with this set statement!!

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

9 Replies
gandalfgray
Valued Contributor

Need help with this set statement!!

Hi

Try adding [ ] around Transport Costs:

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

Not applicable

Re: Need help with this set statement!!

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.

Not applicable

Re: Need help with this set statement!!

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

Not applicable

Need help with this set statement!!

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
Honored Contributor II

Re: Need help with this set statement!!

hei

attach is an example

try this

Not applicable

Re: Need help with this set statement!!

Hi,

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

Thanks

Not applicable

Re: Need help with this set statement!!

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

Not applicable

Re: Need help with this set statement!!

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

chauhans85
Esteemed Contributor

Need help with this set statement!!

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)

Community Browser