topic Re: Find the last week & year in QlikView

Find the last week & year

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

Hi all,

I have concatenated year and week as mentioned in the below

SEMAINE_ANNEE

2016|1

2016|2

2016|3

2016|51

2016|52

2017|1

2017|2

2017|3

Now i want to calculate the stock movement by adding the turnover based on the last three weeks in the selected list and divide the stock quantity of the last week in my selection.

Example: Sum (CA) 2017|1, 2017|2, 2017|3 / (divided) by Sum (STOCK) of 2017|3

Can anyone hint me, how to find the last (MAX) three weeks from the SEMAINE_ANNE selection. and also the last week (MAX)

They are in string format.

Regards

Djega

Re: Find the last week & year

tresB — Tue, 14 Mar 2017 13:17:05 GMT

Recreate a the field with a dual value like:

Dual(SEMAINE_ANNEE,SubField(SEMAINE_ANNEE,'|',1)*100+SubField(SEMAINE_ANNEE,'|',2)) as SEMAINE_ANNEE

And then front end expression like:

=Sum({<SEMAINE_ANNEE={"=Rank(SEMAINE_ANNEE)<4"}>}STOCK)

Once your field is created with dual value, you can directly use any numerical function like max(), min()...

=Max(SEMAINE_ANNEE)

Re: Find the last week & year

muthukumar77 — Tue, 14 Mar 2017 13:17:38 GMT

Hi,

Create Autonumber of SEMAINE_ANNEE order by Year and Week.

It will be like this,

SEMAINE_ANNEE Autonum

2016|1 1

2016|2 2

2016|3 3

2016|51 4

2016|52 5

2017|1 6

2017|2 7

2017|3 8

Sum({$<AutoNum={">=$(=Max(AutoNum)-2)<=$(=Max(AutoNum))"}>}CA) / Sum({$<AutoNum={$(=Max(AutoNum))}>}STOCK)