Not able to get correct percentage when the value is condition based

[#1]

Hi,

I have a scenario,

I need to calculate sum(sales) and percenatge like below

Sales

If(rate>0 then sum(sales),sum(rate*sale))

Percentage

If(rate>0 then sum(sales)/Total Sum(sales),sum(rate*sale)/Total(Sum(rate*sales))

Here the problem is because of condition it is splitting the values into two sets when calculating percentage

Set A                                                                                                               Set B                     

If(rate>0 then sum(sales)/Total Sum(sales)                             sum(rate*sale)/Total(Sum(rate*sales)

So 100%                                                                                                           100%

Is there is any solution to get total as a variable or any other solution for this type of scenarios .

I need total percentage as 100%.(something like column(1)/total coulmn(1))

Please find the attached for application and do the needful.

Regards

Thiru

[MK_QSL · Accepted solution]

May be

sum({<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"}, Time = {"=Frac(Time) >= Time#(vStartTime, 'h:mm TT') and Frac(Time) <= Time#(vEndTime, 'h:mm TT')"}>} IF(Rate=0,Sales, Rate*Sales))

and

sum({<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"}, Time = {"=Frac(Time) >= Time#(vStartTime, 'h:mm TT') and Frac(Time) <= Time#(vEndTime, 'h:mm TT')"}>} IF(Rate=0,Sales, Rate*Sales))

/

sum(TOTAL {<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"}, Time = {"=Frac(Time) >= Time#(vStartTime, 'h:mm TT') and Frac(Time) <= Time#(vEndTime, 'h:mm TT')"}>} IF(Rate=0,Sales, Rate*Sales))

[sunny_talwar]

To avoid if (IF(Rate=0,Sales, Rate*Sales)), we can use this

Sales* RangeMax(1, Rate)

[MK_QSL]

What if Rate is less than 1?

[MK_QSL]

To improve the performance, should create a new temprate field.

If(Rate = 0, 1, Rate) as TempRate

and use as

sum({<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"}, Time = {"=Frac(Time) >= Time#(vStartTime, 'h:mm TT') and Frac(Time) <= Time#(vEndTime, 'h:mm TT')"}>} TempRate*Sales)

and

sum({<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"}, Time = {"=Frac(Time) >= Time#(vStartTime, 'h:mm TT') and Frac(Time) <= Time#(vEndTime, 'h:mm TT')"}>} TempRate*Sales)

/

sum(TOTAL {<YearMonth={">=$(=Date(Vstartdate, 'YYYY/MM/DD h:mm:ss TT'))<=$(=Date(Venddate, 'YYYY/MM/DD h:mm:ss TT'))"}, Time = {"=Frac(Time) >= Time#(vStartTime, 'h:mm TT') and Frac(Time) <= Time#(vEndTime, 'h:mm TT')"}>} TempRate*Sales)

[sunny_talwar]

Then we have a problem

[#7]

HI Manish,

It is correct Answer it is working .

Need to check on small thing here

is it possible to add distinct feature here  like below

IF(Rate=0, distinct Sales, Rate*Sales))

Regards

Thiru

[MK_QSL]

Not sure but try

Distinct Sales * If(Rate = 0, 1, Rate)

[#9]

Hi Manish

This is fine working

distinct  IF(Rate=0, Sales, Rate*Sales))

but i want

IF(Rate=0, distinct Sales, Rate*Sales)) like this

Is it possible?

Regards

Thiru