Qlik Community

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.

Please review the table below:

ProductYearWeek2014.wk32014.wk42014.wk52014.wk5
Product AStartstock50000400003000025000
Endstock40000300002500015000
Product BStartstock5000400030002500
Endstock4000300025001500
Product CStartstock25000200001500012500
Endstock2000015000125007500
TOTALStartstock80000640004800040000
Endstock64000480004000024000

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

YearWeek%SEQ_DATUM_YWSALES
2014.wk254
2014.wk355
2014.wk456
2014.wk557
2014.wk658

StartstockSUM( STOCK ) [This Works]
EndstockSUM({<%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

13 Replies

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

Made a minor type.o

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

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.

Re: Problem set analyses week vs week in crosstable

Please , Load Your sample file...

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

Please see the included casefile.

It should represent the actual case accurately.

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:

WEEKWEEKSEQYEARYEARWEEK
53120132013.WK53
1220142014.WK1
2320142014.WK2
3420142014.WK3
4520142014.WK4
5620142014.WK5
6720142014.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?

Community Browser