8 Replies Latest reply: May 7, 2018 4:06 AM by Helge Jørgensen

# Set analysis - Year-to-date

Hi qlik expert.

I have started to use this master calendar; https://community.qlik.com/docs/DOC-8642 , and now I would like to start using the date-function in qlik. I have tried with different set-analysis but with no success.

I would like the sum of values for year-to date, and previous year to the same date (previous year. 04.05.2018 and 04.05.2017)

I have tried using this expression for YTD.

Sum({<Year=, Quarter=, Month=, Week=, Date={‘>=\$(=YearStart(Today()))<=\$(=Today())’}, ElementID= {RR}>} [Vaerdata.Verdi])

But it doesn’t work. Does some have a suggestion? Do I use wrong master calendar script?

Thanks

Cheers
Helge

• ###### Re: Set analysis - Year-to-date

Try this

Sum({<Year, Quarter, Month, Week, Date = {">=\$(=YearStart(Today()))<=\$(=Today())"}, ElementID = {RR}>} [Vaerdata.Verdi])

or this

Sum({<Year, Quarter, Month, Week, Date = {">=\$(=Date(YearStart(Today()), 'DateFormatHere'))<=\$(=Date(Today(), 'DateFormatHere'))"}, ElementID = {RR}>} [Vaerdata.Verdi])

• ###### Re: Set analysis - Year-to-date

Thanks for the reply, Sunny. Unfortunately, your suggested expressions didn’t work.

by using this expression: sum({<Year= {'2018'}, ElementID = {RR}>} [Vaerdata.Verdi]) I get the correct value, but I would like to learn how to use the date-functions in Qlik.

Do you have any other suggestions of what could be the problem?

• ###### Re: Set analysis - Year-to-date

You may try two things to troubleshoot-

1. Use the date field in filter pane, if its appearing left aligned means its created as text field, so scriptor expressions need to change to interpret source field as date using date# function.

2. Create test table if used this expression in other charts and check the expression heading, u might see set expression is showing something wrong after Date = {}, inside the curly brackets..

• ###### Re: Set analysis - Year-to-date

My dates appers to the right in the filter pane. See attached qvf-file.

• ###### Re: Set analysis - Year-to-date

A copy of the qvf-file is no attached at the my first post.

Your help is highly appreciated!

Cheers Helge

• ###### Re: Set analysis - Year-to-date

May be this

Sum({<

Year = {"\$(=Year(Today()))", "\$(=Year(Today())-1)"},

Quarter,

Month,

Week,

Dato = {"=SetDateYear(Dato, Year(Today())) >= YearStart(Today()) and SetDateYear(Dato, Year(Today())) < Today()"},

ElementID = {RR}>}

[Vaerdata.Verdi])

• ###### Re: Set analysis - Year-to-date

Thanks, Sunny!!

That is exactly what I was searching for!

Cheers Helge