topic Re: how Create DOD Calculation field in straight table in QlikView

how Create DOD Calculation field in straight table

nazaninslp — Sat, 16 Nov 2024 21:47:19 GMT

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

Re: how Create DOD Calculation field in straight table

sunny_talwar — Mon, 03 Dec 2018 12:56:06 GMT

Try using Inter Record Functions here

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

Re: how Create DOD Calculation field in straight table

nazaninslp — Tue, 04 Dec 2018 06:56:40 GMT

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?

Re: how Create DOD Calculation field in straight table

sunny_talwar — Tue, 04 Dec 2018 11:52:39 GMT

What are the manual values you are getting?

Re: how Create DOD Calculation field in straight table

nazaninslp — Tue, 04 Dec 2018 12:50:46 GMT

Dear Stalwar

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

sunny_talwar — Tue, 04 Dec 2018 13:15:48 GMT

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)))
)

Re: how Create DOD Calculation field in straight table

nazaninslp — Wed, 05 Dec 2018 10:29:04 GMT

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

sunny_talwar — Wed, 05 Dec 2018 12:25:31 GMT

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.

Re: how Create DOD Calculation field in straight table

nazaninslp — Wed, 05 Dec 2018 12:55:03 GMT

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

sunny_talwar — Wed, 05 Dec 2018 12:58:24 GMT

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)

Re: how Create DOD Calculation field in straight table

nazaninslp — Wed, 05 Dec 2018 13:04:53 GMT

Excellent
you are GOD of qlikview 😄
Thank youuuuu very muchhhhhhhhhhhhhhh
would you please share some documents or link regarding this topic, honestly I didn't get what you did exactly and why you used the AVG function and asc sorting !

Re: how Create DOD Calculation field in straight table

sunny_talwar — Wed, 05 Dec 2018 13:25:39 GMT

Above function is sort dependent... when you wanted to see above revenue... you wanted to see the above revenue for the same SERVICE_NAME.... so, first sort is by SERVICE_NAME.... next if you are in 2018-12-03... you want to see 2018-12-02 in the previous row... so, second sort is by DATE_KEY...

If you change the sort to DATE_KEY first and then SERVICE_NAME... you will see all 2018-12-02 and then when it ends you will see 2018-12-03... if you look back from 2018-12-03 for one SERVICE_NAME, you will see 2018-12-03 for another SERVICE_NAME... which is not right. Does this make sense?

Avg(1) is just used to show the most recent DATE_KEY (used within the set analysis). Avg(1) will be 1 for the most recent date and 0 for all others.

Re: how Create DOD Calculation field in straight table

nazaninslp — Wed, 05 Dec 2018 14:14:54 GMT

Thank youuuuuuuuuuuuuu so muchhhhhhhhhhhhhhhhhh