Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
Contributor

YTD calculation Logic help needed

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.

YTD Calculation.PNG

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. 

10 Replies
Highlighted

Re: YTD calculation Logic help needed

Are you using Variables with Parameter here?

Highlighted
Contributor

Re: YTD calculation Logic help needed

Hi Sunny,

Yes

Highlighted

Re: YTD calculation Logic help needed

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 

Highlighted
New Contributor III

Re: YTD calculation Logic help needed

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)

Highlighted
Contributor

Re: YTD calculation Logic help needed

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

Highlighted
Contributor

Re: YTD calculation Logic help needed

Hi, Binaykumar,

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

Highlighted

Re: YTD calculation Logic help needed

How do you define your variable?

Highlighted
Contributor

Re: YTD calculation Logic help needed

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))";

Highlighted

Re: YTD calculation Logic help needed

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=))