Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

A ratio as a calculated dimension

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:

Knipsel.PNG

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

1 Reply
Highlighted
Partner
Partner

Re: A ratio as a calculated dimension

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