topic Correct Syntax required in QlikView

Correct Syntax required

shumailh — Wed, 05 Aug 2009 21:05:20 GMT

What is the correct syntax for the below code?


sum({$<LoadDate = {"1/31/2009"}>} LCY_Amount)+
sum({$<LoadDate = {"2/28/2009"}>} LCY_Amount)+
sum({$<LoadDate = {"3/31/2009"}>} LCY_Amount)+
sum({$<LoadDate = {"4/30/2009"}>} LCY_Amount)+
sum({$<LoadDate = {"5/31/2009"}>} LCY_Amount)+
sum({$<LoadDate = {"6/30/2009"}>} LCY_Amount)+
sum({$<LoadDate = {"7/31/2009"}>} LCY_Amount)

Regards,
Shumail Hussain

Correct Syntax required

prieper — Fri, 07 Aug 2009 03:22:43 GMT

Hi,

want do you want to do? The LoadDate is only one date and if on the end of the month you wish to summarize LCY_Amount?

Would a simple Sum(If(LoadDate = Floor(MonthEnd), LCY_Amount)) do the same?

Peter

Correct Syntax required

johnw — Fri, 07 Aug 2009 07:21:11 GMT

If you're looking for the syntax for a list:

sum({<LoadDate={"1/31/2009","2/28/2009","3/31/2009","4/30/2009","5/31/2009","6/30/2009","7/31/2009"}>} LCY_Amount)

If you're looking for all load dates that occur on the end of the month, I'd probably do something like what Peter suggested:

sum(if(LoadDate=daystart(monthend(LoadDate)), LCY_Amount))

Or perhaps create a "Loaded on Last Day of Month?" flag, then:

sum({<"Loaded on Last Day of Month?"={1}>} LCY_Amount)

Or:

sum("Loaded on Last Day of Month?" * LCY_Amount)