topic Re: Same Date Comparison CY (Current Year) x Last Year (LY) in QlikView

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

Tue, 28 Mar 2017 19:22:33 GMT

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)

,Null()

Sum(

{<Year=

,Month=

,Day=

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

>} FLOW))

Expression: FLOW_CY =

Sum(

{<Year=

,Month=

,Day =

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

>} FLOW)

,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)')

(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!

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

sunny_talwar — Tue, 28 Mar 2017 19:29:24 GMT

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)

Tue, 28 Mar 2017 20:06:34 GMT

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)

sunny_talwar — Wed, 29 Mar 2017 01:41:36 GMT

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)

ElizaF — Wed, 29 Mar 2017 08:21:57 GMT

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)

Tue, 04 Apr 2017 17:44:48 GMT

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.