Date table showing last day with first day data

[lanlizgu]

Hi,

I would like to have a table with the date as dimension showing the last date of each year (for the current year it should be the last day of the year).

The thing is that for the expression I would like to have the sum(values) of the first day of the year. 

I am not being able to display as dimension the last day of the year with the sum(values) of the first day of the year.

Any suggestion?

[lanlizgu]

Hi uacg0009. Thanks for your data.
Based on my data:
Dimension: =Aggr(Max([Start Date]),Year)
Expression: sum({<[Start Date]={$(=concat(distinct chr(39) & Aggr(Min([Start Date]),Year) & chr(39), ',' ))}>} Value)


Regarding your data, this doesn't work as I understand that the date is not in date format

[lanlizgu]

I continue not being able to have this, the dimension can't be calculated as in other expressions I have data that needs to be displayed in the date and they are not the last day (first day)

[uacg0009]

Hi,
So what you want in the table only has 2 columns, right? the "Last Date" and "Value"?

I have a method, I put the Year as Dimension, but I hide it.

and then in expression I user

=Date(Aggr(NODISTINCT Max(YearEnd([Start Date])),Name,Year),'YYYY-MM-DD')

and

Sum({<Month = {"1"},Day = {"1"}>}Value)

is that ok?
Aiolos

[lanlizgu]

Hi Aiolos,

Thank you, but it still not working. The first year data doesn't start on January 1st (it starts on February 2nd 2011), so the formula should be the first day of the year with data, not January 1st.

With sum({<[Start Date]={$(=concat(distinct chr(39) & Aggr(Min([Start Date]),AÑO) & chr(39), ',' ))}>} Value) it works correctly (my start date field is already a date as you can see),  

However, in the next step I would also need to add in the date column (Aggr(NODISTINCT Max(YearEnd([Start Date])),Year)) the first day of the data (February 2nd 2011), and with the previous formula I don't see how to add this first date.

I'm thinking about generating a canonical calendar, do you think this would fit?

Thank you.

[lanlizgu]

Do you think there is any chance of integrating the calculated dimension calculus inside the expression so the dimension could be the date without being calculated?

[uacg0009]

Hi,

I think I found another way to solve that, I don't know whether you can accept or not.

I use firstsortedvalue, you can see my image below:

Aiolos

[lanlizgu]

Hi Aiolos,

Thanks for your help. I continue working on it.

The thing is that for the dimension where I show the last day of the year Date(Aggr(NODISTINCT Max(YearEnd([Start Date])),Name,Year),'YYYY-MM-DD') I also need to show the first day of the first year. Do you know how could I achieve this?

Thank you.

[uacg0009]

So what columns and values do you need in the table?
could you please tell me all columns you need? no more add.
Now I know what you want is
1. last day of the year
2. first day of the year
3.value of first day of the year
only this 3 value, right?
if you can give a sample including the data you have
and the result you want, it will be better

[lanlizgu · Accepted solution]

Hi Aiolos,

I was able to develop this last week without adding a calculated dimension.

Based on your document, the expression would be something like this:

sum({
< Date={$(=concat(distinct chr(39) & Aggr(Date(Max(Date), 'DD/MM/YYYY'),Year,Name) & chr(39), ',' ))}>
+
< Date={$(=concat(distinct chr(39) & Aggr(Date(Max(Date), 'DD/MM/YYYY'),Year,Name) & chr(39), ',' ))}>
}aggr(
sum(
{
< Date={$(=concat(distinct chr(39) & Aggr(Date(Min(Date), 'DD/MM/YYYY'),Year,Name) & chr(39), ',' ))}
>
} Value )
,Year,Name))
+
sum({
< Date={"$(=Date(Min(Date), 'DD/MM/YYYY'))"}>
}aggr(
sum(
{
< Date={$(=concat(distinct chr(39) & Aggr(Date(Min(Date), 'DD/MM/YYYY'),Year,Name) & chr(39), ',' ))}>
} Value )
,Year,Name))

In my example it is different as the date field is already a date and I add also in the set analysis a conditional based on other date field.

Thank you.

Best regards.