topic Re: Like for Like in set analyses? in QlikView

Like for Like in set analyses?

Mon, 15 Jul 2013 15:29:38 GMT

Dear QlikView fanatics,

I have a quistion about indirect set analyses: I think it should be possible to do the folowing:

Sum Turnover(#Netvalue_EUR) of this year where store is open(storestatus: active selling point) this periode but also last period. I've tried to use the p() statement but i couldn't make it work.

Does anyone has a clue to get it right?

Re: Like for Like in set analyses?

Mon, 15 Jul 2013 15:33:04 GMT

herewith a code copiable

sum({$<Year={$(=Only(Year))}> *

$<StoreName = p({$<StoreStatus={'Active Selling Point'}>} StoreName)>

+ $<Year = p({$<Year={'2013'}>} Year)>

+ $<Year = p({$<Year={'2012'}>} Year)>

#NetValue_EUR)

Re: Like for Like in set analyses?

swuehl — Mon, 15 Jul 2013 15:36:34 GMT

I am not 100% sure what your requirement is:

"Sum Turnover(#Netvalue_EUR) of this year where store is open(storestatus: active selling point) this periode but also last period."

Could you detail this a bit more (or explain it with some sample lines of data)?

Maybe like

sum(

{$<

Year={$(=Only(Year)),$(=Only(Year)-1)},

StoreName = p({$<StoreStatus={'Active Selling Point'}>} StoreName)

#NetValue_EUR)

Re: Like for Like in set analyses?

Mon, 15 Jul 2013 15:58:12 GMT

Thanks for your quick reply. I'll try to explain in more detail:

For example your expression:

sum(

{$<

Year={$(=Only(Year)),$(=Only(Year)-1)},

StoreName = p({$<StoreStatus={'Active Selling Point'}>} StoreName)

#NetValue_EUR)

This would give me the turnover of selected year + lastyear where stores where open.

But i would like to only sum current year turnover, alltough the store need to be open this year and last year. In the retail industry they call this Like for Like.

Maybe current year and lastyear should be contained in the p() statement where openstores are associated?

I hope its abit more clear this way.

Re: Like for Like in set analyses?

swuehl — Mon, 15 Jul 2013 16:03:53 GMT

Then maybe like

sum(

{$<

Year={$(=Only(Year))},

StoreName = p({$<StoreStatus={'Active Selling Point'}, Year = {$(=Only(Year))}>} StoreName)

* p({$<StoreStatus={'Active Selling Point'}, Year = {$(=Only(Year)-1)}>} StoreName)

#NetValue_EUR)

Re: Like for Like in set analyses?

Mon, 15 Jul 2013 16:12:16 GMT

Holy "..." Thanks Allot!

This does the trick perfectly! I'm wondering though. Is this "performance wise" the best way to fix this?

Re: Like for Like in set analyses?

jozisvk11 — Thu, 16 Jun 2016 09:48:41 GMT

Hello, I have problem with set analyse. I want to filter only values, where is the rows max. I have this, but it is not good.

Sum ({<[Rank of line] = {"$(=max([Rank of line])"} >} [Sum of income]). Thanks for helping me.

Here is my table.

ID Purchase Row Income

N01 1 200

N02 1 50
N02 2 70

N02 3 60

N03 1 10

N03 2 35

N04 1 10

N04 2 40

N04 3 60

N04 4 90

N05 1 20

Set Analayse should choose me only this table.

ID Purchase Row Income

N01 1 200

N02 3 60

N03 2 35

N04 4 90

N05 1 20

Re: Like for Like in set analyses?

swuehl — Thu, 16 Jun 2016 10:15:27 GMT

It would be better if you create a new thread for a new request.

You would need to find the max row per dimension value, so a solution might be:

1 Dimension:

[ID Purchase]

2 Expressions:

=Max(Row)

=FirstSortedValue(Income, -Row)