topic Re: select (?) in QlikView

select (?)

marikabi — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: select (?)

Gysbert_Wassenaar — Mon, 12 Dec 2016 11:47:34 GMT

Try using the FirstSortedValue function:

First: FirstSortedValue( SALES, WEEK )

Last: FirstSortedValue( SALES, -WEEK )

Re: select (?)

marikabi — Mon, 12 Dec 2016 12:16:11 GMT

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

Re: select (?)

Gysbert_Wassenaar — Mon, 12 Dec 2016 12:19:19 GMT

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.

Re: select (?)

marikabi — Mon, 12 Dec 2016 12:25:01 GMT

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)..

Re: select (?)

Gysbert_Wassenaar — Mon, 12 Dec 2016 12:33:40 GMT

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

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

Re: select (?)

marikabi — Mon, 12 Dec 2016 12:38:05 GMT

This works fine

Many thanks