Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
swuehl
MVP
MVP

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)

View solution in original post

7 Replies
Not applicable
Author

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)

swuehl
MVP
MVP

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
Author

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.

swuehl
MVP
MVP

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
Author

Holy "..."  Thanks Allot!

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

jozisvk11
Creator
Creator

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

swuehl
MVP
MVP

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)