Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
MK_QSL
MVP
MVP

So you want to have last week's stock?

Not applicable
Author

Made a minor type.o

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

MK_QSL
MVP
MVP

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
Author

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

But it's not giving a result.

MK_QSL
MVP
MVP

Please , Load Your sample file...

Not applicable
Author

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
Author

Please see the included casefile.

It should represent the actual case accurately.

MK_QSL
MVP
MVP

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
Author

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?