4 Replies Latest reply on Dec 13, 2016 4:24 PM by Shinichiro Murakami

# Need help on LOD calculation

Hello,

Stuck up with a typical calculation that am not not able to get around.

Please do refer to the excel for the data.

Sheet 1: has the list of customers with multiple sales in one bill(or each line). each sales in a line will amount to a fee depending on the combination matrix given in sheet2

e.g. in the line1, customer 1122, made 3 purchases, total commission charged is 1000, now we need to check how many individual sales are there in the line and the sum of each of the commission amount as per the sheet 2.

aim is to verify, if the commission fee paid was more than the contracted commission fee.

• ###### 1. Re: Need help on LOD calculation

Ranjit,

I don't know why you mentioned LOD in title, anyways..

Assuming you have version 9.3 or newer.

Create union with dragging sheet 1 three times.

sheet1, 11, and 12 are there.

[Date union]

if [Table Name]="Sheet1" then [date1]

elseif [Table Name]="Sheet11" then [date2]

elseif [Table Name]="Sheet12" then [Date3]

end

[From to union]

if [Table Name]="Sheet1" then [from Co-Ordinates 1]+" to "+[to cordinates1]

elseif [Table Name]="Sheet11" then [from2]+" to "+[to2]

elseif [Table Name]="Sheet12" then [from3]+" to "+[to3]

end

Fee table as secondary data source(=Master).

[From to]

[Co-ordinate 1]+" to "+[Cordinate 2]

Link two data source with from to

From here, it's not so hard I believe.

Thanks,

Shin

• ###### 2. Re: Need help on LOD calculation

Hello Shin,

Actually am working on 9.2, and i dont find the Union option here

• ###### 3. Re: Need help on LOD calculation

Hello Shin,

Any luck with is issue.

Thanks,Ranjit

• ###### 4. Re: Need help on LOD calculation

It was not easy.

I STRONGLY recommend that you install 9.3.......

=== data 1==

[Event Category]

"Event"+right([Pivot field names],1)

[Field Category]

if contains([Pivot field names],"rom") then "From"

elseif contains([Pivot field names],"To") then "To"

elseif contains([Pivot field names],"Date") then "Date"

end

[Date]

{fixed [Customer],[Event Category]:

min(if [Field Category]="Date" then [Pivot field values] end)}

[From to]

{fixed [Customer],[Event Category]:

min(if [Field Category]="From" then [Pivot field values] end)}

+" to " +

{fixed [Customer],[Event Category]:

min(if [Field Category]="To" then [Pivot field values] end)}

[Filter out Null]

str([Customer])+str([Date])

== Fee table ==

[From To]

[Co-ordinate 1]+" to "+[Cordinate 2]

Thanks,

Shin