I have two tables and one bar/line-chart, with Dimension = Productionpoolgroup
I have three questions.
Question 1.
Table one should get two mini barcharts with values for 1) Posted Items and 2) Physical Costs during the last 10 days, when a day is filtered.
I created two fields/variables:
LET vDayMonth = (day(date(now()))) &'.'&(month(date(now())));
DayNumber &'.'& MonthNumber as DayMonth,
Creating a minichart in a table is not the problem, problem is the expression(s) I should use. I know there are functions like addmonths and addyears, but for days I don’t know. So the expression for Posted Items could look something like this, where ‘addmonths’ and -10 is the problem I think:
Table two should react on the listbox Timefilter, which has two options AllDays and ThisDay. When I select AllDays or ThisDay the values of table two should give the sum of values for 1) Posted Items and 2) Physical Costs for the date filters: that can be year only, year and month, year, month and week and year, month and day.
examples:
-- when I select AllDays and year is 2017, I want the sum of values from the first date until 31-12-2017 where the first date can be in 2015 or 2016
-- when I select AllDays and year is 2017 and month is nov, I want the sum of values from the first date until 31-11-2017 where the first date can be in 2015 or 2016
-- when I select ThisDay and year is 2017, I want the sum of values from 01-01-2017 till Today
-- when I select AllDays and year is 2017 and month is sept, I want the sum of values from 01-01-2017 until 30-09-2017
etc.
When no selection between AllDays and ThisDay is made, the values should always from yesterday
The problem I think is Aggr(Sum(QuantityPosted), but Only in combination with Sum should have an Aggr-function. But expression above don’t work.
Question 3.
The Bar/Line chart should give me for every productionpoolgroup the values for the last ten days and a forecast for the next ten days, when I select a productionpoolgroup, The time filters should have no influence on the values, time filter is yesterday or today -1 or currentday -1. So Date will be the dimension, but how do I get the right expressions?
It is a lot what I’m asking, but I hope someone can help me. Added a little qvw to support my questions.
An AddDate()-function isn't availabe because you could always just add/subtract any number of days from a (real) date. Therefore I suggest to work only with real dates and not with combined date-parts within a string like in your vDayMonth variable.
This would result in your case into something like:
sum({< Date = {">=$(=max(Date)-10)"}>} QuantityPosted)
A bit similar could it be within your second question, like:
sum({< Date = {">=$(=makedate(if(Timefilter = 'AllDays', 2015, max(Year))))<=$(=max(Date))"}>} QuantityPosted)
I'm not sure what is meant with the aggr-part (avoid it until you are realls sure that there is no other way) and if there should be more conditions within the above date-condition. If there are more conditions I would probably outsource this part into a variable, like:
