5 Replies Latest reply: Apr 4, 2017 1:44 PM by Apolo Takeshi Batista

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

Hi there,

I got the following issue:

Variable

vDateLY

=date(if(weekday([vDateMin])

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...

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

• ###### Re: Same Date Comparison CY (Current Year) x Last Year (LY)

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

• ###### Re: Same Date Comparison CY (Current Year) x Last Year (LY)

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

• ###### Re: Same Date Comparison CY (Current Year) x Last Year (LY)

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?

• ###### Re: Same Date Comparison CY (Current Year) x Last Year (LY)

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

:

• ###### Re: Same Date Comparison CY (Current Year) x Last Year (LY)

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.