Same Date Comparison CY (Current Year) x Last Year (LY)

[#1]

Hi there,

I got the following issue:

Variable

vDateLY

=date(if(weekday([vDateMin])

  -weekday(AddYears([vDateMin], -1))>0

  , addyears([vDateMin], -1)

  +(weekday([vDateMin])-weekday(AddYears([vDateMin], -1)))

  , addyears([vDateMin], -1)

  +(7-weekday([vDateMin])-weekday(AddYears([vDateMin], -1)))))

So, when I choose this vDateMin as The date: 01/02/2017 (1st Wed of Feb)

                                   vDateMinLY = 03/02/2016 (1st Wed of Feb)

same logic in vDateMax

and  vDateMaxLY

So I want to show a Graph that shows the FLOW considering CY againist LY

(on a DAY basis, a WEEK basis and a MONTH basis)

Expression: FLOW_LY =

Sum(

  {<Year=

  ,Month=

  ,Day =

  {">=$(=Date(vDateMinLY )) <=$(=Date(vDateMaxLY ))"}

  >}  FLOW)

  =0

  ,Null()

  ,

  Sum(

  {<Year=

  ,Month=

  ,Day=

  {">=$(=Date(vDateMinLY )) <=$(=Date(vDateMaxLY ))"}

  >}  FLOW))

Expression: FLOW_CY =

Sum(

  {<Year=

  ,Month=

  ,Day =

  {">=$(=Date(vDateMin )) <=$(=Date(vDateMax))"}

  >}  FLOW)

  =0

  ,Null()

  ,

  Sum(

  {<Year=

  ,Month=

  ,Day=

  {">=$(=Date(vDateMin )) <=$(=Date(vDateMax))"}

  >}  FLOW))

OK, this part is working.

Now i Need to DISPLAY data.

What should I use as dimension?

I used this as Dimension:

=

If(

  (Day>= '$(vDateMin )' and Day<= '$(vDateMax)')

  or

  (Day>= '$(vDateMinLY )' and Dia <= '$(vDateMaxLY )'),

  if(vClick_Label_Day='day',

  mid(Date(Day),1,5),

  if(vClick_Label_Week='week',

  Week,

  Month

  )

  )

)

The problem:

The day 03/02/2016 will be shown with 03/02/2017, so this is not fully working...

Thx in advance!!

But i think that it is almost it, I just need to figure out how to exhibit the dimension better!

[sunny_talwar · Accepted solution]

Although this is not directly answering what you are asking, but I would suggest you to look into The As-Of Table‌

[#3]

but your idea is using the day with As-Of-Day when I select

          01/02/2017 (1st Wed of Feb)

          it will choose both: 01/02/2017 and 03/02/2016 (1st Wed of Feb)

it this accurate? @stalwar1

[sunny_talwar]

Sort of... It will still show this info next to 01/02/2017... in the background both the dates will be selected.... isn't that what you want?

[ElizaF]

Hi,

Try this solution.

In Calendar  table create new column:

DWY = WeekDay(TempDate)*100+Week(TempDate)*10000+Year(TempDate)

This will create numbers like 52217, 52216, etc. depending on the Date.

And use this column in the expressions, like below:

FLOW_CY = Sum({<Year=,Month= ,Day =, DWY = {$(=max(DWY)) }  >}  FLOW)

FLOW_LY =Sum({<Year=,Month= ,Day =, DWY = {$(=max(DWY -1)) }  >}  FLOW)

Hope helps you.

Eliza

:

[#6]

this doesn't work because the date in which i need to group them is this year. I need to group them in the date 01/01/17

and it shows both dates.. CY and LY..

I solve the problem by loading the whole table again, with different dates like

load day 01 from LY as day 01 from CY.

this solved. but it isn't a good solution.