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: 
Not applicable

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

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!

1 Solution

Accepted Solutions
johnw
Champion III
Champion III

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)

View solution in original post

2 Replies
johnw
Champion III
Champion III

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)

Not applicable
Author

Hi John,

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

Thanks a lot!

Adi