topic Re: Can anyone help me solve this problem? in QlikView

Can anyone help me solve this problem?

sadrisalihu — Sat, 16 Nov 2024 21:50:04 GMT

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.

Re: Can anyone help me solve this problem?

stevedark — Sun, 25 Nov 2018 00:00:24 GMT

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

Re: Can anyone help me solve this problem?

sadrisalihu — Sun, 25 Nov 2018 10:35:40 GMT

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.

Re: Can anyone help me solve this problem?

stevedark — Sun, 25 Nov 2018 11:00:09 GMT

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.

Re: Can anyone help me solve this problem?

sadrisalihu — Sun, 25 Nov 2018 12:33:17 GMT

Thanks again Steverark