Can anyone help me solve this problem?

[sadrisalihu]

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.

[stevedark · Accepted solution]

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

[sadrisalihu]

Thanks a lot Steverark

Your help was great.

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.

Thank you for your help.

[stevedark · Accepted solution]

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:
LOAD DISTINCT
    Field1,
    Field1 as LikeForLike
RESIDENT DataTable;

LOAD DISTINCT
    Field1,
    'Total' as LikeForLike
RESIDENT DataTable;

LOAD DISTINCT
    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.

[sadrisalihu]

Thanks again Steverark