Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
So you want to have last week's stock?
Made a minor type.o
I need next weeks stock (which is the end stock of the current week)
SUM({<%SEQ_DATUM_YWSALES={$(=Max(%SEQ_DATUM_YWSALES)+1)}>}[STOCK])
Here you have used Max(Week) and Now you are adding 1 in that...!!!
Yes; Because I want the startstock of the next week,
But it's not giving a result.
Please , Load Your sample file...
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
Please see the included casefile.
It should represent the actual case accurately.
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 !
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?