Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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)
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)
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)
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.
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)
Holy "..." Thanks Allot!
This does the trick perfectly! I'm wondering though. Is this "performance wise" the best way to fix this?
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
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)