topic Re: Turnover reports - definitions needed? in QlikView

Turnover reports - definitions needed?

Thu, 01 Nov 2012 13:52:40 GMT

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?

Thu, 01 Nov 2012 15:26:12 GMT

Please see the attached QV file

Might be helpful

Regards

Mhatim

Re: Turnover reports - definitions needed?

Thu, 01 Nov 2012 15:49:15 GMT

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?

Vegar — Thu, 01 Nov 2012 16:00:57 GMT

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?

Thu, 01 Nov 2012 16:22:17 GMT

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.

Re: Turnover reports - definitions needed?

Vegar — Thu, 01 Nov 2012 19:15:03 GMT

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)