Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Contributor III

## Can anyone help me solve this problem?

The logic is to take a subtotal for the items who has the value in previous year, so as an example they are the lines I made in yellow.

Labels (1)
• ### test

2 Solutions

Accepted Solutions

Hi,

You should be able to do this with Set Analysis and the P() function.

First I would create variables for vMaxYear and vPriorYear, with expressions like:

=Year(Max(DateField))

and

=vMaxYear-1

The expression for the latest year would be:

sum({<Year={\$(vMaxYear)},Field1=P({<Year={\$(vPriorYear)}>}Field1)>}Value)

The expression for the prior year would then be:

sum({<Year={\$(vPriorYear)},Field1=P({<Year={\$(vMaxYear)}>}Field1)>}Value)

This would allow you to create the second table, with Item 5 and Item 7 missing and the like to like total.

If you require a table with two totals then you have to get a bit more creative. You will have to turn off totals in the chart and calculate the two totals yourself, by creating a new dimension with all items and then the two total lines, and then linking this back into the Field1 dimension.

I describe this approach in this blog post:

https://www.quickintelligence.co.uk/qlikview-accumulate-values/

Hope that points you in the right direction.

Steve

Glad that the Set Analysis worked for you.

The blog post describes what you need to get the two total rows. Basically though you need to create a table that associates to Field1 with a one to one match to each field, and then two totals that link to all.

Something like:

LikeForLike:
Field1,
Field1 as LikeForLike
RESIDENT DataTable;

Field1,
'Total' as LikeForLike
RESIDENT DataTable;

Field1,
'Total Like For Like' as LikeForLike
RESIDENT DataTable;

You can then build your table using LikeForLike as the dimension.

The expression would need ot know whether it was on the total like for like row or any other row. It would need to read something like:

if(LikeForLike = 'Total Like For Like',
sum({<Year={\$(vMaxYear)},Field1=P({<Year={\$(vPriorYear)}>}Field1)>}Value),
sum({<Year={\$(vMaxYear)}>}Value))

Hope that works out for you.

4 Replies

Hi,

You should be able to do this with Set Analysis and the P() function.

First I would create variables for vMaxYear and vPriorYear, with expressions like:

=Year(Max(DateField))

and

=vMaxYear-1

The expression for the latest year would be:

sum({<Year={\$(vMaxYear)},Field1=P({<Year={\$(vPriorYear)}>}Field1)>}Value)

The expression for the prior year would then be:

sum({<Year={\$(vPriorYear)},Field1=P({<Year={\$(vMaxYear)}>}Field1)>}Value)

This would allow you to create the second table, with Item 5 and Item 7 missing and the like to like total.

If you require a table with two totals then you have to get a bit more creative. You will have to turn off totals in the chart and calculate the two totals yourself, by creating a new dimension with all items and then the two total lines, and then linking this back into the Field1 dimension.

I describe this approach in this blog post:

https://www.quickintelligence.co.uk/qlikview-accumulate-values/

Hope that points you in the right direction.

Steve

Contributor III
Author

Thanks a lot Steverark

Please can you help me and how to add a new row like a photo "Total Like To Like" and "Grant Total" at the end.

Glad that the Set Analysis worked for you.

The blog post describes what you need to get the two total rows. Basically though you need to create a table that associates to Field1 with a one to one match to each field, and then two totals that link to all.

Something like:

LikeForLike:
Field1,
Field1 as LikeForLike
RESIDENT DataTable;

Field1,
'Total' as LikeForLike
RESIDENT DataTable;

Field1,
'Total Like For Like' as LikeForLike
RESIDENT DataTable;

You can then build your table using LikeForLike as the dimension.

The expression would need ot know whether it was on the total like for like row or any other row. It would need to read something like:

if(LikeForLike = 'Total Like For Like',
sum({<Year={\$(vMaxYear)},Field1=P({<Year={\$(vPriorYear)}>}Field1)>}Value),
sum({<Year={\$(vMaxYear)}>}Value))

Hope that works out for you.

Contributor III
Author

Thanks again Steverark

Community Browser