Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
i know that Qlik aggerates within the data sheets records where attributes are common. what i need is to control that based on a condition. if the value is less than 150K then aggergate then if not split them out. example below i need FA862517C2001 to show up as 1 record with their totals sumed together. The othe duplicate Contract Numbers are greate than 150K so the need to be seperated.
thank you
Do you have more than one dimensions in your chart above?
yes, i have several more dimensions.
Is it possible that one of those dimension's value is different for the two Contract Number dimension which repeats? When the two rows are combined what value will show up for the particular dimension which have different value in the two rows?
How are you getting the total? Is it calculated at the sheet level or load script?
the other dimensions are the same, the only difference is the measure column "Total". I can get them either all roll up or to all be broken out into unique records, what need is the ability to condition that roll up base on the value of the "total" attribute. All contract numbers that are the same with a "total" greater than 150K roll up all others break apart into separate records.
the totals are just loaded from the data set
Can you try this.
tempData:
LOad * Inline [
dimension, measure
FA862517C2000, 134353.00
FA862517C2001,27090.00
FA862517C2001,101587.50
FA862517C2005,310000.00
FA862517C2005,5590000.00
];
Data:
Load *, dimension&'|'&RowPatNO as AggDimension;
lOAD *,if(dimension=previous(dimension),peek("RowPatNO")+1,1) as "RowPatNO"
Resident tempData order By dimension, measure Desc;
Drop Table tempData;
Then Use this expression to calculate the New measure
IF(measure>=150000, measure, Aggr(Sum(measure), dimension))
Result: