Dynamic Set Analysis Expression

[#1]

Hi

First Post on here so be gentle

I have a need to have a more efficient and dynamic Set Analysis.

Basically the cost element of the product is made up of multiple parts

StdCost + CustomerRebate - SupplierRebate - Uplift

which is pretty straight forward but they need to switch on and off elements of the cost as required which I have done in in the expression using if statements and set analysis and seemed to be working ok even if it was messy. But yesterday a straight table was giving some weird results showing 100% margins on the row level even though the total margin was calculated correctly so i'm thinking that the expression isn't quite right.

=
(
  Sum(
  {$< DocDateMonthAge = {0} >}  
  TotalSales
  )-


  ( // Cost Dynamics  =  Cost + Customer Rebate - Supplier Rebate - Uplifts  '100 + 10 - 10 - 10'
  if(substringcount(concat(Addition),'Std') = 1 
  ,Sum(
  {$< DocDateMonthAge = {0} >}  
  TotalCost
  )
  ,0)
  +
  if(substringcount(concat(Addition),'Customer') = 1 
  ,Sum(
  {$< DocDateMonthAge = {0} >}  
  CustomerRebate
  )
  ,0)
  -
  if(substringcount(concat(Addition),'Supplier') = 1 
  ,Sum(
  {$< DocDateMonthAge = {0} >}  
  SupplierRebate
  )
  ,0)
  -
  if(substringcount(concat(Addition),'Uplift') = 1 
  ,Sum(
  {$< DocDateMonthAge = {0} >}  
  Uplift
  )
  ,0)
  ) 
)
/ Sum(
  {$< DocDateMonthAge = {0} >}  
  TotalSales
  )

What I am thinking is it possible and I have already tried a view ideas is to have one Set Analysis for the cost rather than 4 and switch the expression fields on and off instead.

[sunny_talwar]

Is it possible to share a sample to see the issue instead of just seeing the expression? It tends to get difficult solving issues based off just looking at expressions.

Preparing examples for Upload - Reduction and Data Scrambling

Uploading a Sample

[adamdavi3s]

One way I have done this in the past when I have a known set of different alterations to a figure is to actually load this in to the fact and then dynamically set the formula based on selections.

If you're got a lot of data you're going to take a hit on performance with if statements and in our case it was about 180m rows so adding the additional columns worked better for us.

Qlik actually put me on to this method originally and its proved useful

It is just one way, there are many different ways!

So in your example you would load (plus the other combinations not listed)

TotalCost                                               AS TOTAL_COST

TotalCost  + CustomerRebate                 AS TOTAL_COST_PLUS_CR_LESS_SR_LESS_UL

TotalCost  - SupplierRebate                   AS TOTAL_COST_LESS_CR_PLUS_SR_LESS_UL

TotalCost  - Uplift                                  AS TOTAL_COST_LESS_CR_PLUS_SR_PLUS_UL

TotalCost  + CustomerRebate    - SupplierRebate                                 AS TOTAL_COST_PLUS_CR_PLUS_SR_LESS_UL

Then for your chart expression you set the following variable to use:

='TOTAL_COST_'&if(v_UseCR=1,'PLUS_','LESS_')&'CR'&'_'&if(v_UseSR=1,'PLUS_','LESS_')&'SR'&'_'&if(v_UseUL=1,'PLUS_','LESS_')&'UL'

Please remember to mark this as helpful or the correct answer if I have helped you or answered your question.

[effinty2112]

Hi Liam,

               Your expression looks a bit weird to my eyes, especially parts like:

if(substringcount(concat(Addition),'Std') = 1 ,Sum( {$< DocDateMonthAge = {0} >}TotalCost),0)

I can't say for sure of course, I know nothing about your application, you might be right on the mark so please don't take offense.

If you're not getting the results you want try replacing with something like:

Sum({$< DocDateMonthAge = {0} >*<Addition = {"*Std*"}>}TotalCost) 

(Maybe you can drop the wildcard and go Addition = {'Std'}, depends of course on the nature of the Addition field)

So you'll get something like:

Rangesum(

Sum({$< DocDateMonthAge = {0} >}TotalSales),

-Sum({$< DocDateMonthAge = {0} >*<Addition = {"*Std*"}>}TotalCost),

Sum({$< DocDateMonthAge = {0} >*<Addition = {"*Customer*"}>}TotalCost),

-Sum({$< DocDateMonthAge = {0} >*<Addition = {"*Supplier*"}>}TotalCost),

Sum({$< DocDateMonthAge = {0} >*<Addition = {"*Uplift*"}>}TotalCost))

Rangesum is used because if any part of the sum isnull it will treat it as a zero where Sum() + ... + Sum() would return an error in that case.

I may have sent you on the wrong path, if so apologies.

Kind regards

Andrew

[#5]

Got me all excited then

So the situation is I have a unconnected dimension table called addition which holds 4 values to allow me to switch on and off the cost elements

"if(substringcount(concat(Addition),'Std') = 1",

     Sum( {$< DocDateMonthAge = {0} >}TotalCost)

     ,0)

so if Addition value Std is selected in the field list then the TotalCost is added to the calculation other wise its zero and s

so on for the other cost elements

Modifying you solution returns the full calculation

Rangesum(

Sum({$< DocDateMonthAge = {0} >*<Addition = {"*Std*"}>}TotalCost),+

Sum({$< DocDateMonthAge = {0} >*<Addition = {"*Customer*"}>}CustomerRebate),-

Sum({$< DocDateMonthAge = {0} >*<Addition = {"*Supplier*"}>}SupplierRebate),-

Sum({$< DocDateMonthAge = {0} >*<Addition = {"*Uplift*"}>}Uplift)

)

Because the Addition Table is not connected its a data island

I was aiming for

Sum(  {$< DocDateMonthAge = {0} >}    TotalCost + CustomerRebate - SupplierRebate - Uplift )

with the various elements in bold switched on and off via the addition field selection.

Must admit its not easy trying to explain a first post

I know I'm close but not yet got a cigar

[#6 · Accepted solution]

Eureka

figured it out

Created a variable called vDynamicCost

=Text(if(substringcount(concat(Addition),'Std') = 1

  ,'TotalCost' ,'0') &'+'

  &

  if(substringcount(concat(Addition),'Customer') = 1

  , 'CustomerRebate-' ,'0') &'+'

  &

  if(substringcount(concat(Addition),'Supplier') = 1

  ,'SupplierRebate-'  ,'0') &'+'

  &

  if(substringcount(concat(Addition),'Uplift') = 1

  ,'Uplift' ,'0')

  )

Then my Set Analysis

=Sum({$<DocDateMonthAge = {0}>}$(vDynamicCost))

And it works a treat.

It wasn't till I sent a couple of screen shots in and realised something I had forgotten I had in the Additions table which was the actual field names so tried it with the Concatfunction and added plus and minus to each field where needed and it worked of sorts then progressed to the final solution.

So thanks for the help and for prompting me to look at the problem differently

[effinty2112]

Hi Liam,

               Ah ... the Addition dimension field is from a data island! Your script makes more sense now. I see that I made a mess in the script I gave you, I repeated TotalCost when I meant to edit this and replace where necessary with the correct measures. By way of apology here's a suggestion to simply things further if you want:

Try this data island:

Addition:

LOAD * INLINE [

    Addition, Measure

    Std, +TotalCost

    Customer, +CustomerRebate

    Supplier, -SupplierRebate

    Uplift, -Uplift

];

Your required expression is now:

=Sum($<DocDateMonthAge = {0}>}$(=Concat(Measure)))

This may be a bit easier to maintain.

All the best

Andrew

[#8]

Hi Andrew

Yes that's the first way I tried it and the eureka moment was the screen shot of the table diagram I posted in reply to you.

I have both types of Variables set up as the Inline Table way will return a negative value if only uplift is selected and for reporting they want to see positive values so hindsight suggests I keep both ways available