select (?)

[marikabi]

Hello Guys,

I am trying to estimate the increase/decrease of the sales in the different shops.

I have these columns:

SHOP, SALES, WEEK.

I'd like to calculate the difference (in percentage) of the sales in the different shops, from the first week to the current one.

My problem is that for example in the first/last week we were not selling in all the shops.

I need something like:" select the first and last value different from zero".

How can I translate this in an expression?

Many thanks in advance

[Gysbert_Wassenaar]

Try using the FirstSortedValue function:

First: FirstSortedValue( SALES, WEEK )

Last: FirstSortedValue( SALES, -WEEK )

[marikabi]

Hi,

thank you for your reply..

How can I sum the sales per shop??

in the same week I have more lines related to the same shop, and using the sum function it gives me back an error

[Gysbert_Wassenaar]

If the SALES field contains numbers then sum(SALES) will sum the values. If you get an error then the expression is not correct. Make sure to use CASE sensitive field names. SALES, Sales and sales are three different fields as far Qlikview is concerned.

[marikabi]

Yes, the sales field contains numbers...using this kind of formula it gives me back error.

FirstSortedValue((sum[SALES]), WEEK)

I was also trying to use this different approach:

Sum({$<[WEEK]={"$(=((Min(WEEK))))"}>} [SALES])

Sum({$<[WEEK]={"$(=((Max(WEEK))))"}>} [SALES])

and it works fine, but only if you filter a specific shop. In fact, if you don't filter anything, for the last week it gives back 0.. (maybe because in the data structure I already have the week 50 till 52)..

[Gysbert_Wassenaar · Accepted solution]

Ah, you're trying to nest aggregation functions. That needs an extra function:

FirstSortedValue( aggr(sum(SALES),WEEK, SHOP), Aggr(WEEK,WEEK,SHOP))

[marikabi]

This works fine

Many thanks