How to view data at one shot which is 7 Months in 2018(Jan to July) & 2017(Aug to Dec)

[nevilledhamsiri]

If I am to hold the CTRL Key down & view the figures of 2018 (Jan to Jul) & 2017 (Aug to Dec), how far this is possible? Is there any other way forward?

Thanks

Neville

[Anil_Babu_Samineni]

May be try this?

If(YEAR=Max(YEAR-1),SUM({<YEAR={$(=Max(YEAR-1))}, MONTH=E({<YEAR={$(=Max(YEAR))}>} MONTH)>} VALUE),SUM({<YEAR={$(=Max(YEAR))}>} VALUE))

[nevilledhamsiri]

Only 2018 figures are seen. (from August to December figures in 2017) also requires to be seen

Thanks 

[passionate]

Are you looking for previous 12 months data.

if yes, then you can use as of months technique at script level or use Addmonths in set analysis in front end

[Anil_Babu_Samineni · Accepted solution]

Can you try this?

If(YEAR=2017,SUM({<YEAR={2017}, MONTH=E({<YEAR={2018}>} MONTH)>} VALUE),SUM({<YEAR={2018}>} VALUE))

[nevilledhamsiri]

Yes This produces the out put I need. Can you please explain what & how this formula is all about. So that in next Month I need to take 8 Months from 2018 (Jan-Aug) & 2017 (Sep -Dec) etc. I have no idea  what its syntax are. I will immediately close the tread once I get your more explanation on this

Thaks very much Anil

[vishsaggi]

May be this? I added Aug 2018 value in your excel.

LOAD *, Date(MakeDate(YEAR, MonthSort,'01'),'MM/DD/YYYY') AS DateID;

LOAD YEAR, MONTH, VALUE,

     Pick(Match(MONTH, 'JAN','FEB','MAR','APR','MAY','JUN','JUL','AUG','SEP','OCT','NOV','DEC'), 1,2,3,4,5,6,7,8,9,10,11,12) AS MonthSort

FROM [..\Nivelle\DIFF_YEARS.xlsx] (ooxml, embedded labels, table is Sheet2);

Use this set analysis in your straight table chart.

Dim: YEAR, MONTH

Expr: = SUM({< DateID = {">=$(=AddMonths(Max(DateID),-11))"} >}VALUE)