spotfiretibco

end of month/year data in tibco spotfire

I have the following dataset in tibco spotfire:

Original data

I want to bring the amount of item in the beginning of each month for each row and get the following result:

Image

Any suggestions?

The dataset looks like this: I have got maximum amount for previous month, but I want to get the amount of the last day of the previous month.

enter image description here

Solution

  • I could do it via an intermediate column (as Spotfire's OVER does not accept expressions).

    First calculate the year and month like this: (assuming the format of your date is known and stable) [YearMonth] is :

    Integer(Concatenate(Substring(String([Date]),7,4),Substring(String([Date]),4,2)))

    then your [Previous Month] is:

    Max([Amount]) over (Intersect(Previous([YearMonth]),[BRANCH],[CLASS]))

    and your [Diff] is:

    [Amount] - [Previous Month]

    Revised after comment 19 August:

    change the Max to ValueForMax and insert the day of the month, so that the formula calculates the value corresponding to the last recorded day for that month:

            ValueForMax(DayOfMonth([Date]),[Amount]) over (Intersect(Previous([YearMonth]),[BRANCH],[CLASS]))

    Revised after comments 6 October:

    if Substring(..) is not available, here is an alternative solution:

    Create a new column (to make the expressions understandable): [StringMonth] as:

    If(LongInteger(Month([Date]))>10,String(LongInteger(Month([Date]))), Concatenate('0',String(LongInteger(Month([Date])))))

    Then calculate your [YearMonth] as:

    Integer(Concatenate(Year([Date]),[StringMonth]))