Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
hi
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));
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,
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
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