Calculation issue in the Table and KPI matric

[Veerendra_Katikala]

Hi Experts 

I'm facing an below issue to get correct data in the table, I used following condition in the script but it was not working as expected. it was giving 80% correct data but also producing incorrect data as per the logic which is not expected.

I download and checked the data i noticed following records are incorrect and need to remove from the table and KPI metric should also show correct number. could you please help me out.

Highlighted one's shouldn't be part of the table, the calculation should assume only Avg price > std price then return data

I attached qvf file along with export data. in the export data the one which is TRUE records are not required.

In KPI box I need Material count when following condition get satisfied

(Sum(([GR/IR Spend(GC)]))/Sum(Quantity))>(Sum(StdPricePerPOUOM*Quantity)/Sum(Quantity))

[Lech_Miszkiewicz · Accepted solution]

Hi , 

I am assuming your "check" if price is > than std price is done on the Item level regardles of the year. Looking at the qvf and load script I can see you only load one year of data anyway.

One thing I noticed when testing this data is in fact the way it was loaded. Without any modifications to your data model indeed values appear identical on front end but your IF() conditions says they are not. I then tried to apply some formulas and indeed those values are stored as different values. When rounding to 3 decimal point precision i got this:

so as you can see values are indeed different hence your IF() statement returns this record as Y.

Strangely multiplying values by say 1000 fixes the problem so yo umay want to try that.

cheers

[Veerendra_Katikala]

Thanks for your time 🙂

Even If I use 3 decimal precision there were some records still same price between both metrics. I need to eliminate them by any scenario from the table and count in the KPI box. 

I tried below like this,

KPI Box :

This number (which is in the KPI Box) may not be the correct when compare to the table (after excluding identical prices from both metrics) as this calculation also considering 'N's or Nulls. I need to exclude them in below count and table rows.

Table: 

Here are the conditions that I used.

Avg price:

std price:

Below rows should be removed from the table.

Attached are the export data and qvf file.

Could you please help me with the Calculation to get correct the result in both KPI and Table?

[Veerendra_Katikala]

Thanks Lech for your Help :).

Even if round to 3 decimals my data set contains few more identical values in both metrics, so round with 3 also doesn't helps here. the condition should check accurately even if round to 2,3,4 & 5.. then produce those items which are not identical.

So still looking for some help. 

[marcus_sommer]

I suggest you put each single sum() with/without any rounding as a separate field and then viewing the results in a table-box. The differences should become obvious and probably hinting for their causes.

[Lech_Miszkiewicz]

I actually did that @marcus_sommer by binary loading the model from the provided app. 
I then put all values as dimensions side by side and they looked identical, however when i applied floor(field, 001) on fields which looked identical without it it yielded slightly different values which you can see in one of the screenshots i provided in previous comments. 
This is indeed very strange but i couldn't really troubleshoot it without the source data. 
Do you want to try and explain that phenomenon by looking at provided qvf and maybe having a crack at solving this mystery 😜

cheers

[marcus_sommer]

At the moment I have no Sense available.

The idea behind my suggestion is to look at first on the real existing values - within a table-box. Means the origin calculation as well as all single parts with/without any rounding and showing all available digits - and further this within the context of the relevant key-values + object-dimensions and if necessary also with unique table-keys - rowno().

Any unexpected data/associations should be noticeable and which might be hidden within a chart.

[Veerendra_Katikala]

Your solution would help for few cases but not solving problem, anyways I am approaching data team to create columns in DB side. thanks so much for your time.