Including a static target value for current year in a yearly sales trend overview

Wed, 24 Nov 2010 14:08:09 GMT

Hi Everybody,

I am trying to do the following chart, where the purpose is to compare the same reporting month over several years (e.g. if the user selects July), he will see July 2007 vs. 2008 vs. 2009 vs. 2010 for Actuals. On top of that I would like to include the Year End Budget for the Current Year as a static value, so even if the period of comparison is changing, this value stays the same.

The data is structured as follows:

Country	View	Rep Event	Year	Year View	Month	Sales
A	MTD	Actuals	2007	CY-3	1	100.00
A	MTD	Actuals	2007	CY-3	2	120.00
A	YTD	Actuals	2007	CY-3	1	100.00
A	YTD	Actuals	2007	CY-3	2	220.00
A	MTD	Budget	2007	CY-3	1	110.00
A	MTD	Budget	2007	CY-3	2	130.00
A	YTD	Budget	2007	CY-3	1	110.00
A	YTD	Budget	2007	CY-3	2	240.00
A	MTD	Actuals	2008	CY-2	1	80.00
A	MTD	Actuals	2008	CY-2	2	100.00
A	YTD	Actuals	2008	CY-2	1	80.00
A	YTD	Actuals	2008	CY-2	2	180.00
A	MTD	Budget	2008	CY-2	1	100.00
A	MTD	Budget	2008	CY-2	2	120.00
A	YTD	Budget	2008	CY-2	1	100.00
A	YTD	Budget	2008	CY-2	2	230.00

So the static value I would like to include for Budget is where View = YTD and Rep Event = Budget and Year View = CY and Month = 12.

For the actual Sales I am using the following expression:

SUM({$<Year = >}IF( [Rep Event]='Actuals', "Sales", 0) )

For the budget Sales I am using the following expression:

sum({$<Year = {2010}, Month = {12}, View = {'YTD'}>} IF( [Rep Event]='Budget', "Sales", 0))

But if I do that, I need to build the chart based on 2 dimensions: Year and Reporting Event, which does not look very nice, as for the previous years Budget will always be empty.

Is there a smarter way to do this?

Thanks for your help!

Including a static target value for current year in a yearly sales trend overview

johnw — Wed, 24 Nov 2010 23:16:43 GMT

I'm afraid I'm not understanding. I am particularly unclear about why you need to build a chart based on two dimensions when you don't want to build a chart based on two dimensions. Perhaps a sample QVW would help?

The one thing that I can see, though, is that you don't need if() in either expression. Including the condition in the set analysis itself should be more efficient:

sum({<Year=,[Rep Event]*={'Actuals'}>} Sales)
sum({<Year={'2010'},Month={'12'},View={'YTD'},[Rep Event]*={'Budget'}>} Sales)

Including a static target value for current year in a yearly sales trend overview

Fri, 26 Nov 2010 09:07:26 GMT

Hi John,

you are right, I do not need 2 dimensions and with your help, it works fine now.

Thanks a lot!

Adi

topic Including a static target value for current year in a yearly sales trend overview in QlikView

Including a static target value for current year in a yearly sales trend overview

Including a static target value for current year in a yearly sales trend overview

Including a static target value for current year in a yearly sales trend overview