Compare two periods thanks to range

[marakud971]

Hi everybody,

I need help.

What i want is to be able to compare 2 periods which are not calendar years.

example: 11/19 to 08/20 compare to 11/18 to 08/19 

I managed to display the current slice ( 11/19 to 08/20 ) but I can't do it for the previous slice (11/18 to 08/19 )

 

 I can't seem to overlay the graphics. and on the gray graph, the dates are not sorted

Any ideas ?

Thanks a lot.

[edwin]

Hi, one way is to link current month with prior year month.  assuming data in on month level, the association will be like the following:

FactMonth	DisplayMonth	DateType
11/19	11/19	CurrentYear
11/18	11/19	PriorYear
12/19	12/19	CurrentYear
12/18	12/19	PriorYear
01/20	01/20	CurrentYear
01/19	01/19	PriorYear

when you chart it, your Dimension is DisplayMonth; you will have two expressions with set analysis ...DateType={'PriorYear'}...  this will plot 11/19 to 8/20; second expression  with DateType={'NextYear'} to plot 11/18 to 8/19

do you think this is something that will work for you?

[PriyankaShivhare]

implemented the similar requirment using Attached QVF found on community

check it out

[marakud971]

Hi @edwin . Thank you for your reply.

How could i build your table configuration in qlikview script ?
In my table i have a field for Year and Month.

Thks

[marakud971]

Hi @PriyankaShivhare , thanks a lot.

I'm on qlikview. I think your file is for qliksense.

Do you have the qlikview version ?

Best,

[PriyankaShivhare]

there is no difference in Qlikview and Qliksense in terms of scripting.

[marakud971]

oki. But how could i open the file if i don't have qliksense ?
I only have qlikview at my client.
I can't use the script

[edwin]

this assumes you are always comparing Months and your calendar year starts November of the year:

Data:
LOAD date(date('11/1/2019') +(RowNo()-1)*7) as Date, floor(rand()*1000) as Sales
AutoGenerate(48*4);


calendar:
load distinct Date, MonthStart(Date) as Month resident Data;

tmpBridge:
load distinct Month resident calendar;

inner join (tmpBridge)
load Month as DisplayMonth Resident tmpBridge;

Bridge:
load Month, DisplayMonth,year(addmonths(DisplayMonth,-10)) as DisplayYear, 'CURRENT' as DateType Resident tmpBridge where Month=DisplayMonth;

concatenate(Bridge)
load Month, DisplayMonth,year(addmonths(DisplayMonth,-10)) as DisplayYear, 'PRIORYEAR' as DateType Resident tmpBridge where Month=addmonths(DisplayMonth,12);

drop table tmpBridge;

 you can have a list of Calendar Years, select 2019 and select 11/2019 to 8/2020:

first tables shows relationship between the months, second table shows the raw data, third chart shows the expected output, you can then make it a line chart if you want but look at the expressions.

you can expand this using set analysis on Display Month.  if the year calculation is a stretch - you dont have to do that.  just do your set analysis and the relationship will persist.  personally i would do month indices as its simpler but that a different topic

[Kushal_Chawda]

@marakud971  No need of complex coding here. See the attached. You can do it via calculated dimension  with single inline table. 

[marakud971]

hi @Kushal_Chawda , I need to overlay the two graphics