1 Reply Latest reply: Nov 21, 2017 2:47 AM by Marcus Sommer RSS

    expressions for two tables and a bar/linechart

    Court van de Lisdonk

      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

       

        • Re: expressions for two tables and a bar/linechart
          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