topic Re: Expression Calculation in QlikView

Expression Calculation

Tue, 25 Feb 2014 09:50:10 GMT

Hi!

i am currently calculating late deliveries KPI.

it is made up of two factors

1. Delayed Orders (#Late) =1 + 2. Changed Orders (Code Ä3, Ä2,Ä1)

(if(Act_deldate >$(Rolling 1) or Act_deldate <=$(maxDate),

if( #Late = 1,

or ChangeCode = 'Ä1' or ChangeCode = 'Ä2' or ChangeCode ='Ä3',

%InLevNr)))

However my syntax fails me!

Any suggestions?

Best

Bradley

Re: Expression Calculation

hic — Tue, 25 Feb 2014 11:32:19 GMT

You have a comma too much (after "#Late=1" ). Try this instead:

Count(

if(Act_deldate >$(Rolling 1) or Act_deldate <=$(maxDate),

if( #Late = 1 or ChangeCode = 'Ä1' or ChangeCode = 'Ä2' or ChangeCode ='Ä3',

%InLevNr

)

HIC

Re: Expression Calculation

Tue, 25 Feb 2014 13:20:12 GMT

Hi Henric Thank you for your suggestion however it is still showing the total amount for all the months even when using the $Variable Rolling 1 or Rolling 12

Variables are defined accordingly - >

Rolling 1 = AddMonths(max(Act_deldate),-1)

Rolling 12 = AddMonths(max(Act_deldate),-12)

see below

should i perhaps use the rolling statements as flags in the script or is my logic incorrect in the statement, right now having a hard time figuring out why this is is not working for the count functions, however for the totals ie R1 R6 R12 Month Total is correct, but Late is not.

Best

Brad

Re: Expression Calculation

hic — Tue, 25 Feb 2014 13:46:17 GMT

Now I understand what you are trying to do...

Since you use Month as dimension, you cannot reach transactions belonging to other months than the current one. At least not with a standard If() function. The Aggregation scope is that specific month only.

Read more about the aggregation scope here:

The Aggregation Scope

Read more about what to do to create rolling numbers here: ( Above() or Aggr() - or both )

Accumulative Sums

Calculating rolling n-period totals, averages or other aggregations

HIC