A ratio as a calculated dimension

qlikhalmar — Wed, 16 Nov 2016 21:03:12 GMT

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

Re: A ratio as a calculated dimension

lironbaram — Wed, 16 Nov 2016 21:32:15 GMT

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

topic Re: A ratio as a calculated dimension in QlikView

A ratio as a calculated dimension

Re: A ratio as a calculated dimension