Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
byrnel0586
Creator
Creator

Rounding within Set Analysis

Hi,

I am trying to use the rounding function within this set analysis expression and it isn't working correctly. I am not sure if I have the round() in the right place, but I have gotten this to work in my other expressions that do not have set analysis in them. I want to round the final result of the "if not" part of the statement. I also want the rounding to only occur from July forward, but you can see in the trending  that it is rounding all months prior as well.

Any help is appreciated.

=if(num(ReportDate) < '43312', SUM({<Year = {2018}, Month>}Aggr(IF(
num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
/
SUM({<Year = {2018}, Month>}RptCardWeight),
//this begins the "if not" expression
round(SUM({<Year = {2018}, Month>}Aggr(IF(
num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
/
SUM({<Year = {2018}, Month>}RptCardWeight),0.1))

1 Solution

Accepted Solutions
byrnel0586
Creator
Creator
Author

Got it. Thanks.

if(Only({<Year = {2018}, Month>}ReportDate) < '43312',SUM({<Year = {2018}, Month>}Aggr(IF(
num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
/
SUM({<Year = {2018}, Month>}RptCardWeight),

round(SUM({<Year = {2018}, Month>}Aggr(IF(
num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
/
SUM({<Year = {2018}, Month>}RptCardWeight),0.1))

View solution in original post

6 Replies
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

You can use round function before your if statement.

=Round(If.....)

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!
sunny_talwar

Have you checked if you if condition is working.... May be try this and see if before july you are able to get 1 and after july it should be 2?

=If(ReportDate < '43312', 1, 2)

byrnel0586
Creator
Creator
Author

Thank you, but where would I end the round function with 0.1? I tried this and now it's not calculating anything for July but isn't rounding the months prior.

=round(if(num(ReportDate) >= '43312',0.1,

SUM({<Year = {2018}, Month>}Aggr(IF(
num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
/
SUM({<Year = {2018}, Month>}RptCardWeight)),

SUM({<Year = {2018}, Month>}Aggr(IF(
num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
/
SUM({<Year = {2018}, Month>}RptCardWeight))

byrnel0586
Creator
Creator
Author

Yes, the condition is working in my other expressions. For example:

=if(num(ReportDate) < '43312',
SUM(Aggr(
IF($(RoundedRate%)>=num#(PtileBeg) and $(RoundedRate%)<= num#(PtileEnd)
,
Star) * Only(RptCardWeight)
,
Star,_MeasureYearKey))
/
SUM( RptCardWeight),
round(SUM(Aggr(
IF($(RoundedRate%)>=num#(PtileBeg) and $(RoundedRate%)<= num#(PtileEnd)
,
Star) * Only(RptCardWeight)
,
Star,_MeasureYearKey))
/
SUM( RptCardWeight) ,0.1))

byrnel0586
Creator
Creator
Author

Hi Sunny,

You are right, it is something wrong with the condition. Since it is a trending chart using set analysis, this condition only works when selecting the month. I need to create an 'if' using set analysis I think...

byrnel0586
Creator
Creator
Author

Got it. Thanks.

if(Only({<Year = {2018}, Month>}ReportDate) < '43312',SUM({<Year = {2018}, Month>}Aggr(IF(
num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
num#(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
/
SUM({<Year = {2018}, Month>}RptCardWeight),

round(SUM({<Year = {2018}, Month>}Aggr(IF(
num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))>= num#(Only({<Year = {2018}, Month>}PtileBeg)) and
num#(round(SUM({<Year = {2018}, Month>}NUMERATOR)/SUM({<Year = {2018}, Month>}DENOMINATOR),0.01))<= num#(Only({<Year = {2018}, Month>}PtileEnd)), Only({<Year = {2018}, Month>}Star)) *
Only({<Year = {2018}, Month>}RptCardWeight), Star,_ThresholdMeasureYearKey,Month))
/
SUM({<Year = {2018}, Month>}RptCardWeight),0.1))