Generating counts in an expression

[#1]

Hi gang - very new to Qlikview and need some simple (?) assistance

I have a status field in a fact table - based around email delivery (well - actually a StatusID - which links to a Dimension :))

The status field has a number of values e.g. 'sent', 'bounced','delivered','opened','clicked' etc.

I need to add an expression that does the following;

SUM(Opened) / SUM(Delivered)

This will give me an 'Opened %' for each Email campaign

Help please

Mark...

[#2]

Your expression it´s ok, just put as dimension a field has a status!!!!

[#3]

sorry - poor explantion from me

What is need is;

((Count(1) where [Email status] = 'Opened') / (Count(1) where [Email status] = 'Delivered'))

is probably a better explantion 🙂

[oscar_ortiz]

This is a crude solution, but you can try something like this.

Sum( if([Email Status] = 'Opened', 1, null()) ) / Sum( if([Email Status] = 'Delivered', 1, null()) )

Preferably I'd rather take care of that in the load script creating a counter for my "Devlivered" and "Opened".

if([Email Status] = 'Opened', 1, null()) ) as [Open Counter],

if([Email Status] = 'Delivered', 1, null()) ) as [Delivered Counter]

Then you can simplify your expression.

Sum([Open Counter]) / Sum([Delivered Counter])