5 Replies Latest reply: Nov 1, 2012 3:15 PM by Vegar Lie Arntsen

Turnover reports - definitions needed?

I need to produce monthly turnover reports, showing sales figures for Year To Date, YTD (2011), YTD (2010), total 2011, and total 2010. What definitions do I need to use in 'Expressions' in order to be able to produce this 5 column report?

thanks!

• Re: Turnover reports - definitions needed?

Please see the attached QV file

Regards

Mhatim

• Re: Turnover reports - definitions needed?

Thanks Mhatim, I tried using your definition but unfortunately it didn't work! My current ones are:

YTD: Sum({\$<Year={\$(=Max(Year))}>} \$(vKPI))

2011: Sum({\$<Year={\$(=Max(Year)-1)}>} \$(vKPI))

2010: Sum({\$<Year={\$(=Max(Year)-2)}>} \$(vKPI))

so now I need two extra columns for YTD 2011 and YTD 2010. Any idea?

• Re: Turnover reports - definitions needed?

Try

```sum( -InYear(date,'2011-01-01',0)*\$(KPI)) //YTD 2011
sum( -InYear(date,'2010-01-01',0)*\$(KPI)) //YTD 2010
```

Best regards

Vegar Lie Arntsen

QlikView consultant at egbs consulting ab

Blog (in Swedish): bi-effekten.se

• Re: Turnover reports - definitions needed?

Sorry, I pasted the wrong expression.

You should try using the function

YearToDate (timestamp [, year_offset=0 [, start_month = 1 [, ref_timestamp]]])

Sum(-1 * YearToDate (date , 0, -1 , today()) * \$(KPI)) // YTD last year (2011)

Sum(-1 * YearToDate (date , 0, -1 , today()) * \$(KPI)) // YTD 2 yrs ago  (2010)

• Re: Turnover reports - definitions needed?

Can you please post sample QV.

I hope by YTD 2011 you mean the total turnover from 1st of 2011 till the same time as today in 2011.

If that is the case the expression in the QV that I attached should work.

You need to create a variable for 2011 and 2012.For 2011 it should be today()-366 and then you can use that

in the expression with if ...If Date>=01/01/2011 and <= the variable then Sum (Turnover).

I not sure if that helps you

Regards

Mhatim.