Last month's day value issue

[#1]

Hello everyone!

I have this kind of table:

How can I have a third column with values of the last day of each month? Something like this:

Thanks in advance!

[jonathandienst]

If you have a Month field and you add that as a dimension, it is simply:

     Sum(TOTAL <Month> Value)

[dickelsa]

What you can do is:

Temp_1

Load

Date,

Month(Date) as Month,

Day(Month) as Day,

Value

Resident <Table> Order by Date DESC;

next you can use Peek function to see if the month's differentiate and if the day is different, and create a new column

Best regards,

Dick

[#4]

I need this in the load script, because I'll create an apply map with this table, related to this third column

[#5]

Actually, what I need is a new dimension (created in load script), not a simple column by set analysis.

[sorrakis01]

Hi,

Try this:

Datos:

LOAD * INLINE [

    Date, Value

    01/07/2015, 423

    10/07/2015, 567

    15/07/2015, 678

    20/07/2015, 45

    31/07/2015, 678

    01/08/2015, 4576

    10/08/2015, 657

    14/08/2015, 432

    21/08/2015, 6789

    27/08/2015, 213

    31/08/2015, 3467

];

Mapping:

MAPPING LOAD

Month(Date) as Month,

Sum(Value) as ValueAggr

Resident Datos

group by Month(Date);

Datos2:

LOAD Date,

Month(Date) as Month,

ApplyMap('Mapping',Month(Date)) as ValueAggr,

Value

Resident Datos;

Drop table Datos;

Regards,

[jonathandienst]

Marcelo Hernandes wrote:

I need this in the load script, because I'll create an apply map with this table, related to this third column

Are you sure that is a good idea? It will limit the ability of the users to slice and dice the data. It is simple enough to calculate in the front end.

[maximiliano_vel · Accepted solution]

Values:

LOAD Date,

     Value,

     Month(Date) as Month

;

LOAD * INLINE [

  Date, Value

  07/01/2015, 423

  07/10/2015, 567

  07/15/2015, 678

  07/20/2015, 45

  07/31/2015, 678

  08/01/2015, 4576

  08/10/2015, 657

  08/14/2015, 4132

  08/21/2015, 6789

  08/27/2015, 213

  08/31/2015, 3467

];

JOIN

LOAD Month as Month,

     Max(Date) as Date,

     1 as IsMaxDate

RESIDENT Values

GROUP BY Month;

LEFT JOIN

LOAD Month,

     Value as LasDayValue

RESIDENT Values

WHERE IsMaxDate =1;

DROP Field IsMaxDate From Values

[Kushal_Chawda]

try this

Data:

LOAD date(MakeDate(2014)+IterNo()-1,'DD-MM-YYYY') as Date,

monthname(MakeDate(2014)+IterNo()-1) as MonthName,

Ceil(Rand()*100+1000) as Value

AutoGenerate(1)

While IterNo()-1 <= Today()-MakeDate(2014);

Left Join

LOAD Distinct MonthName ,

max(Date) as Date,

1 as MaxDateFlag

Resident Data

Group by MonthName;

Left Join

LOAD Distinct MonthName,

sum(Value) as MaxValue

Resident Data

where MaxDateFlag=1

Group by MonthName;

[#10]

Thanks, but the ValueAggr gives the month's sum values, not the last day value of each month:

What I expect is to see is the value 678 in all July days. And 3467 in all August days.

Ps: here, the data format is DD/MM/YYYY