Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
petermoyaert
Contributor III
Contributor III

View YTD figures and full month figures

In the selection table : I select january 2012 and the YTD flag. In the first colum I have the sales untill today for january 2012. The next colum shows the sales for the same period but in 2011. In a next column I would like to see the sales of the full month january 2011, so that I can see how much we still have to sell to have equal sales as full month last year. Any suggestions ?

3 Replies
Anonymous
Not applicable

Hi Peter

Try this for YTD for last year:

Sum({$<Year={$(=Max(Year)-1)},

   datum={'<=$(=AddYears(Today(),-1))'}

>}  Sales)

And then you can use this for the full month:

Sum({$<Year={$(=Max(Year)-1)}>}  Sales)

Where Year =  Year(datefield) 

And set Month(datefield) as your dimension

This way you can use a 3th expression like:  Column(2)- Column(1)  to show you the difference.

Good luck,

Dennis.

rajni_batra
Specialist
Specialist

Use inmonth function

INMonth(Date, BaseDate,-12)

Take  current date as Base Date

petermoyaert
Contributor III
Contributor III
Author

Dear Dennis
For the month figures untill today I use the following formula :
sum({$<Jaar = {$(=max(Jaar)) }, Maand = {"<=$(=max({<Jaar={$(=max(Jaar))}>} Maand))>$(=max({<Jaar={$(=max(Jaar))}>} Maand)-1)" }>} [bruto] )
For the month figures of last year till same date as today but last year, I use the following formula :
sum({$<Jaar = {$(=max(Jaar) - 1)}, Maand = {"<=$(=max({<Jaar={$(=max(Jaar))}>} Maand))>$(=max({<Jaar={$(=max(Jaar))}>} Maand)-1)"}>} [bruto])
Depending on whether I select the YTD flag I get full month figures of january last year or figures untill 12/01/2011.
I would like to have both in a separate column