topic Re: qlikview scripting query in QlikView

qlikview scripting query

Sunil_26 — Fri, 05 Aug 2022 04:38:06 GMT

Hi,

How to get previous quarter for ex:

i have 2022Q1 ,the previous quarter is 2021Q4, but for me getting 2022Q0,because i have use variables

left(datefield,4)&'Q'&ceil(Month(datefield/3)) for current quarter

left(datefield,4)&'Q'&ceil(Month(datefield/3))-1 for previous quarter

Re: qlikview scripting query

Mark_Little — Fri, 05 Aug 2022 06:47:05 GMT

HI @Sunil_26

You need to adjust the date not the final result something like the below.

left(Addmonths(datefield-3),4)&'Q'&ceil(Month(Addmonths(datefield,-3)/3))

Re: qlikview scripting query

Sunil_26 — Fri, 05 Aug 2022 06:49:27 GMT

ok i will try and let you know if it works

Re: qlikview scripting query

Sunil_26 — Fri, 05 Aug 2022 07:04:08 GMT

hi is this works for only one qurter what if i change the quarter to 2022Q2, i need dynamically expresssion

Re: qlikview scripting query

marcus_sommer — Fri, 05 Aug 2022 07:31:32 GMT

IMO the easiest way to address such tasks is to use a running counter for the period field. This could be done with autonumber() within the master-calendar, like:

load *, autonumber(Year & Q) as RunningQ;
load *, ceil(Month / 3) as Q;
load *, year(Date) as Year, month(Date) as Month;
load date(from - 1 + recno()) as Date autogerate bis- from;

Similar stuff might be also done against YearMonth or YearWeek. Each needed period-field should be created there - in each case as numeric and if needed also as strings or dual() values. The numeric ones might not be shown anywhere but they are needed to calculate/compare anything against each other.

Beside this you could also create a working counter with something like this:

Year * 4 + Quarter as RunningQ

- Marcus