Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Are you using Variables with Parameter here?
Hi Sunny,
Yes
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
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)
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
Hi, Binaykumar,
thanks for your reply. I have already tried your condition.
How do you define your variable?
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))";
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=))