Prior year for year_month field

[#1]

Hi all,

sorry for this newbie question but somebody can explain me how to get the prior year (same month) value for the field Year-Month?

Example: 2016-02 I have to see in the Prior column the value for 2015-02.

Thanks in advance!

Regards

[Kushal_Chawda]

try below expression for Prior Month. Make sure that YearMonth Field is not a string

=sum({<Year_Month= {"$(=date(addmonths(max(Year_Month),-1),'YYYY-MM'))"}>})Amount

[sunny_talwar]

You can try this approach as well:

Vol_Avanz_GG:

LOAD Date#(Year_Month, 'YYYY-MM') as Year_Month,

  Qta

INLINE [

Year_Month, Qta,

"2015-01", 20,

"2015-02", 210,

"2015-03", 2310,

"2016-01", 10

"2016-02", 10

"2016-03", 10

"2014-02", 10

];

Link:

LOAD Year_Month as ReportYearMonth,

  Year_Month,

  'CY' as Flag

Resident Vol_Avanz_GG;

Concatenate(Link)

LOAD Year_Month as ReportYearMonth,

  AddYears(Year_Month, -1) as Year_Month,

  'PY' as Flag

Resident Vol_Avanz_GG;

Expressions

1) =Sum({<Flag = {'CY'}>}Qta) -> Current Year

2) =Sum({<Flag = {'PY'}>} Qta) -> Prior Year

Data Model

[#4]

Thanks Kushal, but this set analysis will work just for one Year_Month, the 2016-02, that is in fact the Max(Year_Month) - 1 of my whole data:

Actually I need that the calculation is made for all the rows.

Thanks.

Giovanni

[#5]

Thanks Sunny.

This soluction is working but... ii will perform 3 load of table Vol_Avanz_GG, that is made of 4M+ of rows.

Regards,

G

[sunny_talwar]

I  don't find this to be an issue just because you won't be repeating the 4M+ rows, but just repeat the dates twice, which should not be too many rows of data

[#7]

Hi Sunny, your solution is working but i want see also the previous year when i select one year.

For example: if i select 2016-01, i want see in the table also 2015-01.

With your solution 2015-01 is zero.

can somebody help me?

[sunny_talwar]

I don't think that should happen, but let me test it out

[sunny_talwar]

I would not make selection in Year_Month field and instead make selection in ReportYearMonth field

[#10]

Thanks Sunny, now your solution is working correctly