expressions for two tables and a bar/linechart

[pacoli2013]

Hello,

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:

= Sum({<DayMonth ={">=$(=date(addmonths(date#('$(vDayMonth)','DD.MM'-10),' DD.MM ')) <=$(=date#('$(vMonthYear)',' DD.MM '))"}, Year=,Month=>} QuantityPosted )

Question 2.

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

I tried:

= if(Only(TOTAL {1<DayIntervalName = p(DayIntervalName)>} DayIntervalID) = 1, Only(Aggr(Sum(QuantityPosted), ProductionPoolGroup), 
Only({$<Year=, Quarter=, Month=, MonthShortName=, Week=, Day=, DayName=, DayShortName=, Date = {"$(='<=' & Date(Max(Date)))"}>} Sum(QuantityPosted)))

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.

Thanks in advance

Regards Court

[marcus_sommer]

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:

pick(match(Timefilter, 'Alldays', ...),

     expr1,

     expr2,

     ...)

- Marcus