YTD calculation Logic help needed

[piyush_s11]

Hi,

I want to calculate YTD. For ex : If I select Month as Sep 2019 then I should get value from Jan-19 to Sep-19.

I have written below code for this

=$(vAmount(Month={">=$(=YearStart(Max(Month)))<= $(=Date(Max(Month)))"},items = {'cost_plus','profit_split','fee_spread'},_Region=,BU_CODE=)) 

the highlighted portion is for ytd calulation. but it is not working. could please assist what is going wrong in the formula.

in above is the screenshot of the same formula. in this before <= sign the default qlik functions are not highlighting in blue color like yearstart & max function is not highlighting in blue. after this sign the qlik functions are highlighting in blue.

is there any error in formula ?. Please note Month field is a text value not in date format.

Thanks. 

[sunny_talwar]

Are you using Variables with Parameter here?

[piyush_s11]

Hi Sunny,

Yes

[sunny_talwar]

This is because of commas which are being interpreted as a separator for multiple parameters. Try something like this Problem when I send parameters to variable function with a set analysys with two conditions 

[BinayKumarSah]

Please have a look this may guide you

Current year

sum({<Year={$(=Only(Year))},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"}>}Income)

For Previous Year

sum({$<Year={$(=Only(Year)-1)},Month= {"<=$(=max({<Year={$(=Only(Year))}>} Month))"}>}Income)

[piyush_s11]

Hi Sunny,

thanks for your valuable input. unfortunetly I am not able to understand that Replace function. could you please guide me what exactly I need o change in my formula. I have pasted complete formula.

thanks in advance

[piyush_s11]

Hi, Binaykumar,

thanks for your reply. I have already tried your condition.

[sunny_talwar]

How do you define your variable?

[piyush_s11]

I have defined variable in the script only

Set vAmount = "Num(Sum({<New_items =,sub_account={'EXPENSE_TYPE'}, sub_account_short_description=,$1,$2,$3,$4,$5,$6,$7,$8,$9>} amount)/$(vCurrencyFormat),$(vNumFormat))";

[sunny_talwar]

Try this

Set vAmount = "Num(Sum({<New_items =,sub_account={'EXPENSE_TYPE'}, sub_account_short_description=,$(=Replace($1,'#',',')),$(=Replace($2,'#',',')),$(=Replace($3,'#',',')),$(=Replace($4,'#',',')),$(=Replace($5,'#',',')),$(=Replace($6,'#',',')),$(=Replace($7,'#',',')),$(=Replace($8,'#',',')),$(=Replace($9,'#',','))>} amount)/$(vCurrencyFormat),$(vNumFormat))";

and then this

=$(vAmount(Month={">=$(=YearStart(Max(Month)))<= $(=Date(Max(Month)))"},items = {'cost_plus'#'profit_split'#'fee_spread'},_Region=,BU_CODE=))