Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Like for Like in set analyses?

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?

 

 

set analyses.PNG

1 Solution

Accepted Solutions
MVP
MVP

Re: Like for Like in set analyses?

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)

7 Replies
Not applicable

Re: Like for Like in set analyses?

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)

MVP
MVP

Re: Like for Like in set analyses?

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)

Not applicable

Re: Like for Like in set analyses?

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.

MVP
MVP

Re: Like for Like in set analyses?

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)

Not applicable

Re: Like for Like in set analyses?

Holy "..."  Thanks Allot!

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

jozisvk11
Contributor

Re: Like for Like in set analyses?

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

MVP
MVP

Re: Like for Like in set analyses?

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)

Community Browser