topic Re: Quarter variables not holding any data in QlikView

Quarter variables not holding any data

Tue, 22 Sep 2015 12:39:37 GMT

Dear QlikComm,

Hey guys, I'm having some difficulty with my quarter variables.

if I make a table with quarters I get the data fine, but when I define variables to compare the quarters I don't get any data.

My quarters are filled with

LOAD

'Q' & Ceil(Month(datum)/3) as Quarter

FROM

And I have defined the following variables:

Let vCurrentQtr = ceil(month(Today())/3);

Let vPreviousQtr = if(Mod(fabs(vCurrentQtr-1),4)=0, 4, Mod(fabs(vCurrentQtr-1),4));

let vCurrentQuarterNumber = lookup( 'QuarterCounter', 'DateId', date(today(), 'DD/MM/YYYY'), 't1' );

But if I use the following expression to fill a table I get 0's

(sum({<Quarter={'$(vPreviousQtr)'}>} HRM_Paginaweergaven))

Does anyone know why that is? Thanks...

Rob

lironbaram — Tue, 22 Sep 2015 12:43:15 GMT

first in your field of quarter you have a string Q1,Q2,..

and in your variables you only defined the number

you can add to your variable this

Let vPreviousQtr ='Q' & if(Mod(fabs(vCurrentQtr-1),4)=0, 4, Mod(fabs(vCurrentQtr-1),4));

Tue, 22 Sep 2015 13:24:13 GMT

Hey Liron,

Yess thnx, that seems to be the sollution for the vCurrenQtr, but the vPreviousQtr is still returning a 0.

I think I will have to try a different approach to the previousQtr. Thanks for the quick reply,

Tue, 22 Sep 2015 14:41:27 GMT

Hey guys, I'm still not able to get any data from the previous quarters.

The current quarter works fine:

Let vCurrentQtr = 'Q' & ceil(month(Today())/3);

But the previous Quarter returns a 0:

Let vPreviousQtr ='Q' & if(Mod(fabs(vCurrentQtr-1),4)=0, 4, Mod(fabs(vCurrentQtr-1),4));

Does anyone know why this is or does anyone have a alternative formula?

Thanks, Rob

lironbaram — Tue, 22 Sep 2015 19:37:21 GMT

hi robert

what i usually do in this cases i use the quarterstart function in my calendar

to create a field with the first date of the quarter for each date

then using the addmonths function it always works

in your case your variables will be

Let vCurrentQtr = quarterstart(Today());

Let vPreviousQtr =addmonths(quarterstart(Today()),-3);

another advantage of this method , is it works between year no need for if statement