Measure Range Calculation

[kavirao]

Hi 

I do have use case where I need to generate a report based on 2 custom variable selection.

I am calculating the sum of sales based on 2 selections which are variable input not a dimentions

1) Sales Type - options are (Direct , Indirect , Others) based on selection it will calculate sum

sum(if($(vsaletype)  =1  , Direct , if($(vsaletype) = 2, indirect , others)))

2)Sales Range - Options are ( < 100000 , > 100000 , 100000 - 1000000, 1000000 -2000000,2000000-3000000)

I need to include this range into after calculate sum of sales using salestype. 

Let me know any solutions on this

Thank you

[kavirao]

Hi 

I do have use case where I need to generate a report based on 2 custom variable selection.

I am calculating the sum of sales based on 2 selections which are variable input not a dimentions

1) Sales Type - options are (Direct , Indirect , Others) based on selection it will calculate sum

sum(if($(vsaletype)  =1  , Direct , if($(vsaletype) = 2, indirect , others)))

2)Sales Range - Options are ( < 100000 , > 100000 , 100000 - 1000000, 1000000 -2000000,2000000-3000000)

I need to include this range into after calculate sum of sales using salestype. 

Let me know any solutions on this

Thank you

[stevejoyce]

First of all I would variablize the field you want to use:

vSalesTypeMeasure as:  = if($(vsaletype)  =1  , 'Direct' , if($(vsaletype) = 2, 'indirect' , 'others'))

Your regular expression would be: Sum( $(vSalesTypeMeasure)).

Your ranges depends on what you are doing, i would need more details or you may be able to figure out now.  You can use class() or if-statement to put into your ranges.

[kavirao]

Existing Script :  

if($(vCurrFormat) = 2, Sum(if($(vSpendType) = 1,[SSpend1] , if($(vSpendType) = 2, [SSpend1], [TotalSpend] ))) ,

dual(num(Sum(if($(vSpendType) = 1,[SSpend1] , if($(vSpendType) = 2, [SSpend2], [Total Spend] ))) /
pow(10,3*floor(log10(Sum(if($(vSpendType) = 1,[SSpend1] , if($(vSpendType) = 2, [SSpend2], [Total Spend] ))) )/3)),'#,###.#' &
pick(floor(log10(Sum(if($(vSpendType) = 1,[SSpend1] , if($(vSpendType) = 2, [SSpend2], [Total Spend] ))) )/3),'K','M','B')),
Sum(if($(vSpendType) = 1,[SSpend1] , if($(vSpendType) = 2, [SSpend2], [Total Spend] ))))

)

On top of this I need to add vSpendRange which has values like below.  Now i need to do on select of range above expression has to be calculate. I know we can do if but seems to be it will be more time consumes to load. any suggestion's