topic Re: Compare two periods thanks to range in QlikView

Compare two periods thanks to range

marakud971 — Sat, 16 Nov 2024 17:55:50 GMT

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.

Re: Compare two periods thanks to range

edwin — Fri, 23 Oct 2020 01:25:39 GMT

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?

Re: Compare two periods thanks to range

PriyankaShivhare — Fri, 23 Oct 2020 02:19:18 GMT

implemented the similar requirment using Attached QVF found on community

check it out

Re: Compare two periods thanks to range

marakud971 — Fri, 23 Oct 2020 02:44:59 GMT

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

Re: Compare two periods thanks to range

marakud971 — Fri, 23 Oct 2020 12:27:18 GMT

Hi @PriyankaShivhare , thanks a lot.

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

Do you have the qlikview version ?

Best,

Re: Compare two periods thanks to range

PriyankaShivhare — Fri, 23 Oct 2020 12:33:21 GMT

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

Re: Compare two periods thanks to range

marakud971 — Fri, 23 Oct 2020 15:26:31 GMT

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

Re: Compare two periods thanks to range

edwin — Sat, 24 Oct 2020 01:59:42 GMT

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

Re: Compare two periods thanks to range

Kushal_Chawda — Sat, 24 Oct 2020 15:23:48 GMT

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

Re: Compare two periods thanks to range

marakud971 — Sun, 25 Oct 2020 18:46:09 GMT

hi @Kushal_Chawda , I need to overlay the two graphics

Re: Compare two periods thanks to range

Kushal_Chawda — Mon, 26 Oct 2020 10:16:45 GMT

@marakud971 like this?

Re: Compare two periods thanks to range

Kushal_Chawda — Mon, 26 Oct 2020 10:17:02 GMT

@marakud971 updated attachment

Re: Compare two periods thanks to range

edwin — Mon, 26 Oct 2020 11:39:57 GMT

@Kushal_Chawda that was awesome expression. i guess mine is just an alternative/different style

Re: Compare two periods thanks to range

marakud971 — Mon, 26 Oct 2020 12:53:53 GMT

thanks @edwin and @Kushal_Chawda , i'll try all of them to learn more !