topic Problem set analyses week vs week in crosstable in QlikView

Problem set analyses week vs week in crosstable

Wed, 30 Apr 2014 07:09:42 GMT

Hi,

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

Please review the table below:

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

Re: Problem set analyses week vs week in crosstable

MK_QSL — Wed, 30 Apr 2014 07:12:40 GMT

So you want to have last week's stock?

Re: Problem set analyses week vs week in crosstable

Wed, 30 Apr 2014 07:14:03 GMT

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

MK_QSL — Wed, 30 Apr 2014 07:18:22 GMT

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

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

Re: Problem set analyses week vs week in crosstable

Wed, 30 Apr 2014 07:22:06 GMT

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

MK_QSL — Wed, 30 Apr 2014 07:25:07 GMT

Please , Load Your sample file...

Re: Problem set analyses week vs week in crosstable

Wed, 30 Apr 2014 07:32:47 GMT

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,

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

Wed, 30 Apr 2014 07:54:08 GMT

Please see the included casefile.

It should represent the actual case accurately.

Re: Problem set analyses week vs week in crosstable

MK_QSL — Wed, 30 Apr 2014 08:20:06 GMT

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 !

Re: Problem set analyses week vs week in crosstable

Wed, 30 Apr 2014 08:29:47 GMT

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?

Re: Problem set analyses week vs week in crosstable

Wed, 30 Apr 2014 08:31:38 GMT

Also: Shouldn't max(Week) be looking at the week that comes with the displayed YearWeek in the crosstable?

Re: Problem set analyses week vs week in crosstable

Wed, 30 Apr 2014 08:46:06 GMT

ok i looked at the qvw you posted and there is nothing wrong in you set analysis ;-9. the thing is that you selected the week 3,4,5 and if you look now at the weekseq your possible selection is 4,5,6 so if you use in set analysis max(weekseq + 1) then the result of this would be 7. and you have no data for the weeseq 7.

i hope this helps!

regards,

Re: Problem set analyses week vs week in crosstable

Wed, 30 Apr 2014 09:00:51 GMT

I now solved it by making a calculation in my script. I had hoped to prevent that. But that does work

Re: Problem set analyses week vs week in crosstable

MK_QSL — Wed, 30 Apr 2014 09:02:16 GMT

What Magdalena has explained, i explained two times 🙂