how Create DOD Calculation field in straight table

[nazaninslp]

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

[nazaninslp]

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 !

[sunny_talwar]

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.

[nazaninslp]

Thank youuuuuuuuuuuuuu so muchhhhhhhhhhhhhhhhhh