A ratio as a calculated dimension

[qlikhalmar]

Hi all,

I have a lot of data, Costs and revenues. Together they make margin. What I want is to make a barchart with in the X-axes a categorised margin and in the Y-axis the number of contracts. it is like this:

My epression for the margin is like this:

(SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Revenue])

- SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Costs]))

/SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Revenue])

The expression for the number of contracts is just:

COUNT({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}DISTINCT [ContractNo])

Can anyone bring me in the right direction??

Thanks,

Halmar

[lironbaram]

you need to create a calculated dimension

the will go something like this

num(subfield(class(aggr(

(SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Revenue])

- SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Costs]))

/SUM({<%Date = {">=$(=MINSTRING({Periode_1}%Date))<=$(=MAXSTRING({Periode_2}%Date))"}>}[Revenue]),[ContractNo]),0.1),'<',1),'#,##0%')

and use as expression your expression for counting contracts from above