# QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

## Problem set analyses week vs week in crosstable

Hi,

I'm having some trouble getting my Set Analyses formula to work.

 Product YearWeek 2014.wk3 2014.wk4 2014.wk5 2014.wk5 Product A Startstock 50000 40000 30000 25000 Endstock 40000 30000 25000 15000 Product B Startstock 5000 4000 3000 2500 Endstock 4000 3000 2500 1500 Product C Startstock 25000 20000 15000 12500 Endstock 20000 15000 12500 7500 TOTAL Startstock 80000 64000 48000 40000 Endstock 64000 48000 40000 24000

Please consider that my date table has a numeric sequence available for weeknumbers:

 YearWeek %SEQ_DATUM_YWSALES 2014.wk2 54 2014.wk3 55 2014.wk4 56 2014.wk5 57 2014.wk6 58

 Startstock SUM( STOCK ) [This Works] Endstock SUM({<%SEQ_DATUM_YWSALES={\$(=Max(%SEQ_DATUM_YWSALES)+1)}>}[STOCK]) [Does not work]

Startstock: Should show this weeks startstock (=stock)

Endstock: Should show next weeks startstock

What am I doing wrong?

Using Before(Stock) won't work properly, when only a few weeks are selected.

Thanks.

Minor tweak about what a formula should say

Het bericht is bewerkt door: Mark Added example file

Tags (6)
13 Replies
MVP

## Re: Problem set analyses week vs week in crosstable

So you want to have last week's stock?

Not applicable

## Re: Problem set analyses week vs week in crosstable

I need next weeks stock (which is the end stock of the current week)

MVP

## Re: Problem set analyses week vs week in crosstable

SUM({<%SEQ_DATUM_YWSALES={\$(=Max(%SEQ_DATUM_YWSALES)+1)}>}[STOCK])

Here you have used Max(Week) and Now you are adding 1 in that...!!!

Not applicable

## Re: Problem set analyses week vs week in crosstable

Yes; Because I want the startstock of the next week,

But it's not giving a result.

MVP

Not applicable

## Re: Problem set analyses week vs week in crosstable

your max(%SEQ_DATUM_YWSALES) will return 58 so if you ad 1 you will have 59 and according to your data you dont have the week 59 so of course it can not give you a correct answer.

do you have a week selected or how to you get the value of this week??

regards,

MT

Not applicable

## Re: Re: Problem set analyses week vs week in crosstable

It should represent the actual case accurately.

MVP

## Re: Problem set analyses week vs week in crosstable

You will never get a result as Max Week is 53 and if you want for next week which is 54.

Week 54 is not in your data model.

You definitely having Date field in your database.

Try to create Week(Date) as Week in your script and also, if you have more than one year data, try to create a sequence ID (ie. WeekID) in the script iteself.

Now you can use the same but believe me, you will never get result for Max(Week) + 1 as this logic is wrong.

This will only work if you hide your chart and keep a condition that it will be only visible if any week is selected.

Even though, if user select last week (i.e. Max(Week)) there would be no data available !

Not applicable

## Re: Problem set analyses week vs week in crosstable

I think you are viewing the fomula and data incorrectly;

The date table is (in the example file) as follows:

 WEEK WEEKSEQ YEAR YEARWEEK 53 1 2013 2013.WK53 1 2 2014 2014.WK1 2 3 2014 2014.WK2 3 4 2014 2014.WK3 4 5 2014 2014.WK4 5 6 2014 2014.WK5 6 7 2014 2014.WK6

The user Selects a Year and a set of weeks. (2014 & Week 2 - 5)

The table then displays 2014.WK2, 2014.WK3, 2014.WK4 and 2014.WK5

The formula works on weekseq. Which is a numeric sequence which goes over boundaries of years.

In the examplefile you fill see: SUM({<WEEKSEQ={\$(=Max(WEEKSEQ)+1)}>}[STARSTOCK])

As you can see the logic goes to WEEKSEQ. And that data should be available.

Or do I misunderstand?