Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
@Kushal_Chawda that was awesome expression. i guess mine is just an alternative/different style
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?
implemented the similar requirment using Attached QVF found on community
check it out
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
Hi @PriyankaShivhare , thanks a lot.
I'm on qlikview. I think your file is for qliksense.
Do you have the qlikview version ?
Best,
there is no difference in Qlikview and Qliksense in terms of scripting.
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
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
@marakud971 No need of complex coding here. See the attached. You can do it via calculated dimension with single inline table.
hi @Kushal_Chawda , I need to overlay the two graphics