CODEWITHSUNDEEP
datespotfire
Nathan
Nathan

Reputation: 23

How to populate all cells with a value taken from last day of the month?

I'm new to Spotfire and I need to populate all cells in a column with a value taken from the last day of the month. Example as below.

Original data:

enter image description here

Required output is that there will be another column that takes 32212 for the month of November ie this column to take the volume that corresponds to the last date 11/20/2009 and for the month of December it will take 12231.

Here is what I did:

  1. Created a calculated column to find the last entry of the month 

    Max([Date]) OVER UID

    and UID is an unique id for each month integer(Year([Date])*100 + Month([date]))

  2. Another calculated column to find the value for the last entry in each month

    if(([Date]) = ([Max Date per Month]),[Volume],0)

  3. Now this is the problem. I tried to use 

    Sum(if(([Date])=([Max Date per Month]),[Volume],0)) OVER ([Max Date per Month])

    but Sum doesn't work.

Any idea?

Upvotes: 0

Views: 673

Answers (1)

Bart
Bart

Reputation: 151

What do you mean by "Sum doesn't work"?

Here is what I did:

  1. Created two calculated columns as

    Month([Date]) Year([Date])

  2. Created Last day in month column which simply gives me latest date in each month

    Last([Date]) OVER ([Year],[Month])

  3. Created column which calculates what you need

    Sum(if([Last Day in Month]=[Date],[Volume],0)) OVER ([Year],[Month])

Upvotes: 0

Related Questions