sum({$<YTD = {1} , rEVENUE={rEVENUE}>}Amount)/Rate return Null , How to make it display 158,000 ?

[paulyeo11]

Hi All

I have below expression return correct value :-

Sum (if(YTD,sales/Rate))

I try to convert from sales to rEVENUE as below :-

sum({$<YTD = {1} , rEVENUE={rEVENUE}>}Amount)/Rate

But the above expression return null value , It should be around 158,000

Hope some one can advise me.

Paul

[paulyeo11]

my QVW

[#3 · Accepted solution]

Hi Paul,

First of all, the YTD field has only 2 values 0, -1. That's why your expression returns 0

There's also a YTD_Flag field which has value 0,1

I don't know if you mean to use YTD_Flag=1 for this expression sum({$<YTD_Flag= {1} , rEVENUE={rEVENUE}>}Amount)/Rate

Or YTD=-1  for it sum({$<YTD= {'-1'} , rEVENUE={rEVENUE}>}Amount)/Rate

[paulyeo11]

Hi Nhu Ngo

Many thank for spot YTD have 0 or -1 , After i change to -1 for YTD , immediately i get the figure i want, So now it have help solve my issue.

I am not able to solve it using SET expression to compute LY YTD month closed. ( 2015 Jan till May ) now is june 20.

And now manage to get LY rEVENUE  by using LY_YTD=-1 , which is Jan till May rEVENUE.

So finally i found one great benefit on compute LY YTD month closed using below load script :-

If(Num([TempDate]) >= $(vYearStartLY) and Num([TempDate]) < $(vMonthNowLY), -1, 0)

As LY_YTD,

Paul