topic Re: Rounding within Set Analysis in QlikView

Rounding within Set Analysis

byrnel0586 — Thu, 31 Aug 2017 14:17:20 GMT

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))

Re: Rounding within Set Analysis

kaushiknsolanki — Thu, 31 Aug 2017 14:22:23 GMT

Hi,

You can use round function before your if statement.

=Round(If.....)

Regards,

Kaushik Solanki

Re: Rounding within Set Analysis

sunny_talwar — Thu, 31 Aug 2017 14:23:51 GMT

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)

Re: Rounding within Set Analysis

byrnel0586 — Thu, 31 Aug 2017 14:31:57 GMT

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))

Re: Rounding within Set Analysis

byrnel0586 — Thu, 31 Aug 2017 14:32:53 GMT

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))

Re: Rounding within Set Analysis

byrnel0586 — Fri, 01 Sep 2017 13:21:52 GMT

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...

Re: Rounding within Set Analysis

byrnel0586 — Fri, 01 Sep 2017 13:38:05 GMT

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))