Forum Discussion

feyd's avatar
feyd
Frequent Visitor
7 years ago
Solved

Calculate value difference from column of a same table with hierarchy from another table

Hi everyone,

 

I have to calculate value difference between rows of the same column in one table where "File Name" column from "Database" table has "File for comparison" file from "DIM_File" table. I've tried with USERELATIONSHIP and it worked, but I was unable to filter the result by "Group".

 

What I'm trying to get -> Difference=sum(F2[Amount])-sum(A1[Amount])

or visually

GroupProdcut CodeAmount F2Amount A1Difference
X1001900190
Y2005013080

 

 

Model looks like this

Database table sample:

File NameDateProduct CodeAmount
A11.jan.2018.20050
A11.jan.2018.1000
A11.feb.2018.20080
F21.jan.2018.20050
F21.jan.2018.100100
F21.feb.2018.2000
F21.feb.2018.10090
B11.jan.2017.200110
B11.jan.2017.100100
B11.feb.2017.20080
B11.feb.2017.10090

 

DIM_File table sample:

File NameYearFile for comparisonFile type
A12018 A
F22018A1A
B12017 B

 

DIM_Product table sample:

Product CodeGroup codeName
2001Prod1
1002Prod2
3001Prod3

 

DIM_Group table sample:

Group codeGroup
1X
2Y

 

Thanks!