Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
nazaninegogo
New Contributor III

how Create DOD Calculation field in straight table

Dears

I want to calculate of DOD (20181202-20181201/20181201) and MOM f my REVENUE trend based on each service and date_key

but my expression is not working 😞

would you please help me in this regards:

Dimension: DATE_KEY, SERVICE_NAME

Expression

sum({<DATE_KEY={"$(=Date(max(DATE_KEY),'YYYY-MM-DD'))"}REVENUE)- sum({<DATE_KEY={">=$(=Max(DATE_KEY)-1)<=$(=Max(DATE_KEY))"}REVENUE)/sum({<DATE_KEY={">=$(=Max(DATE_KEY)-1)<=$(=Max(DATE_KEY))"}REVENUE) * 100

 

 

Labels (3)
1 Solution

Accepted Solutions

Re: how Create DOD Calculation field in straight table

Try this

Expression

(Sum({<DATE_KEY>} REVENUE)/Above(Sum({<DATE_KEY>} REVENUE)) - 1) * Avg({<DATE_KEY = {"$(=Date(Max(DATE_KEY), 'YYYY-MM-DD'))"}>}1)

Sorting

1st by SERVICE_NAME
2nd by DATE_KEY (ascending order)
12 Replies

Re: how Create DOD Calculation field in straight table

Try using Inter Record Functions here

Sum({<DATE_KEY = {"$(=Date(Max(DATE_KEY), 'YYYY-MM-DD'))"}>} REVENUE)/Above(Sum({<DATE_KEY>} REVENUE)) - 1
nazaninegogo
New Contributor III

Re: how Create DOD Calculation field in straight table

Dear Stalwar

as I used your expression, I faced with below table and I checked the values manually and they are not correct

would you please help me?

 

dod2.PNG

 

dod.PNG

Re: how Create DOD Calculation field in straight table

What are the manual values you are getting?
nazaninegogo
New Contributor III

Re: how Create DOD Calculation field in straight table

Dear  

for example for the revenue for 20181203 is (32079000) and for 20181202 is (32157000).
I want the DOD of it.
DOD= 20181203-20181202/20181202 *100
DOD= 32079000-32157000/32157000 *100 DOD= -0.024 %
But as I checked my straight table , it is wrong.

I attached my file .

Re: how Create DOD Calculation field in straight table

Do you have QV12 or above? If you do, then try this

Only({<DATE_KEY = {"$(=Date(Max(DATE_KEY), 'YYYY-MM-DD'))"}>}
Aggr(
Sum({<DATE_KEY>} REVENUE)/Above(Sum({<DATE_KEY>} REVENUE)) - 1
, SERVICE_NAME, (DATE_KEY, (NUMERIC)))
)
nazaninegogo
New Contributor III

Re: how Create DOD Calculation field in straight table

Dear Stalwar

it is working fine on Qlikview 12.

but the issue is that our qlikview server supports QV v.11 😞

is there a way to launch it on qlikview 11?

Re: how Create DOD Calculation field in straight table

To fix this in QV11, you will need to sort your DATE_KEY field in ascending order in the script... once you do that... the problem should be resolved.
nazaninegogo
New Contributor III

Re: how Create DOD Calculation field in straight table

Dear Stalwar
I sorted the DATE_KEY in SORT Tab as ascending but still I get error in expression. 😞

Re: how Create DOD Calculation field in straight table

Try this

Expression

(Sum({<DATE_KEY>} REVENUE)/Above(Sum({<DATE_KEY>} REVENUE)) - 1) * Avg({<DATE_KEY = {"$(=Date(Max(DATE_KEY), 'YYYY-MM-DD'))"}>}1)

Sorting

1st by SERVICE_NAME
2nd by DATE_KEY (ascending order)