Discussion Board for collaboration regarding Qlik DataMarket.
There is a main table FY18 and we have created a resident table tab1 to use GROUP BY function.
Text(if(Sum("Total Revenue")<0.25,'Tail Account (Rev< 1 Mn)',
if(Sum("Total Revenue")>=1.2,'Large Account (Rev> 5 Mn)',
If(Sum("Total Revenue")>=0.25 and sum("Total Revenue")<1.2,'Middle Account (Rev 1-5 Mn)')))) as account
Group by CDG , Customer Group;
I want that grouping should perform over the data of maximum Quarter.
Therefore I use,
but this gives error.
I would first get the max quarter first:
Load Max([Quarters]) as MaxQuarter
Let vMaxQuarter = Peek('MaxQuarter');
Drop Table Temp;
Then, in your where clause you could use
where Quarters = $(vMaxQuarter)
I hope this helps,
View solution in original post
Please refer below thread:
Where clause with max date in load script
Thanks a lot.
But Quarter value is Q2.
Then how it would be able to calculate max of Q2 as it is a string . ??
When you create the quarter in FY18, do it as a Dual field and assign a number to it, for example:
If you have a DateField to use where quarters are calculated
Dual('Q'&Num(Ceil(Num(Month(DateField))/3)),QuarterStart(DateField)) as Quarters
or if you don't have that date but just the Q#, you could rewrite Quarters with something like this:
Dual(Quarters,Right(Quarters,1)) as Quarters
Ideally you should also have the Year with the quarter, but all depends what's available in your data
Max(Quarters) is going to bring a number so you need to redefine that number as a string, for example with 'Q'&Max(Quarters) so you can use it later