en
Welcome Guest! To enable all features please Login or Register.
Options
Go to last post Go to first unread
SO  
#1 Posted : Friday, November 4, 2016 8:50:14 AM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

I want to count all item rows pr vendor ID in a crosstab with all vendors with a subtotal for each vendor, but I don't get the result I want.

I have a crosstab with Vendor number as the "highest" level in my crosstab, then I have receipt ID and item number rows, as a measure I have quantity. I have made a comparison with "qty received late" and "qty received on time", the comparison is placed on the top axis so I get two qty columns. One receipt id can have many item lines, sometimes all lines are received on time or late and sometimes some are late and some are on time on the same receipt id.

Then I have made two calculated columns:
"Count late" -> count(d-2, 0, m1), this returns 0 or 1 as wanted. (I also tried instead if sum(d-2, 0, m1) = 0 then 0 else 1, this returns the exact same result)

"Count on time" -> count(d-1, 0, m1), also returns 0 or 1 as wanted. (I also tried instead if sum(d-1, 0, m1) = 0 then 0 else 1, this returns the exact same result)

In the total for each vendor I want to count the total of "Count late" and "Count on time", but since I only get "1" as a total in the standard total calculation in "Count late" and "Count on time" I made two new calculated columns to hold the totals and then hide the "count late" and "count on time" columns.

So I made the columns to hold the total like this:
"Late receipts" sum(c1, all(c), m1)
"On time receipts" sum(c2, all(c), m1)

But this calculation doesn't count all the item rows, what am I doing wrong? If a receipt ID has several lines received late or early it counts as one, but I want to count the single item rows in the receipt.

(In addition I also made two more calculated columns to calculate the "% of late receipts" and "% of on time receipts", but since the two previous columns doesn't count the rows as I want these are also wrong)

Edited by user Friday, November 4, 2016 8:55:25 AM(UTC)  | Reason: Not specified

Páll Bjarkason  
#2 Posted : Friday, November 4, 2016 9:07:24 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

Could you submit a picture of the crosstab?
Best regards

Páll, CTS
SO  
#3 Posted : Friday, November 4, 2016 9:28:20 AM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

Originally Posted by: Pá Go to Quoted Post
Could you submit a picture of the crosstab?

Yes, but the forum doesn't have an option for uploading a picture. I can't access tinypic.
Páll Bjarkason  
#4 Posted : Friday, November 4, 2016 10:39:20 AM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

Hi SO

Have you tried some other providers facilitating free image upload and hosting for integration on forums e.g. https://postimage.org/ ?

After uploading the image, copy the "Direct Link".
Lastly use the insert image icon on this forum and paste the "Direct Link".


UserPostedImage

Edited by user Friday, November 4, 2016 10:52:14 AM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
SO  
#5 Posted : Friday, November 4, 2016 10:54:11 AM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

Originally Posted by: Pá Go to Quoted Post
Hi SO

Have you tried some other providers facilitating free image upload and hosting for integration on forums e.g. https://postimage.org/ ?

After uploading the image, copy the "Direct Link".
Lastly use the insert image icon on this forum and paste the "Direct Link".


UserPostedImage


crosstab

Edited by user Friday, November 4, 2016 10:55:17 AM(UTC)  | Reason: Not specified

SO  
#6 Posted : Friday, November 4, 2016 10:57:27 AM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

See picture above. The "qty late" and "qty on time" are my comparison, everything else is calculated columns.
Páll Bjarkason  
#7 Posted : Friday, November 4, 2016 12:41:22 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

I assume that you can reference the right most comparison element via sum(d-1,0,m1)
If you have to go one level up then you can do this: count(d-1, all(c), m1)
If you want to go 2 levels up then you can reference then last count calculation and this time summing the subtotals sum(d-1, all(c), m1)

Best regards

Páll, CTS
SO  
#8 Posted : Thursday, November 10, 2016 8:21:05 AM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

Originally Posted by: Pá Go to Quoted Post
I assume that you can reference the right most comparison element via sum(d-1,0,m1)
If you have to go one level up then you can do this: count(d-1, all(c), m1)
If you want to go 2 levels up then you can reference then last count calculation and this time summing the subtotals sum(d-1, all(c), m1)



I'm stuck, so I made a totally stripped down crosstab without any comparison, calculations or changes in visibility or visibility agents.

Do you have any idea how I can solve it? I basically want to count the number of "late" item rows and "on time" item rows for each vendor with a total for each vendor and a grand total for all vendors. In addition I want to make a % calculation to calculate the % of "late" and "on time" for each vendor and grand total.

My data source is from data modeler, so I can manipulate the source data there if needed.

crosstab
Páll Bjarkason  
#9 Posted : Monday, November 14, 2016 5:06:44 PM(UTC)
Páll Bjarkason
Rank: Advanced Member

Groups: extranet\Forum
Joined: 1/13/2012(UTC)
Posts: 164

Hi So

I have tried to mimic your screenshot using the demo data Finance cube provided form TARGIT.
The exercise consists of counting correctly across hierarchies, i.e. subtotals and totals.
Hence, this rather cumbersome solution is proposed.
The cumbersomeness stems from the required temporary calculation per crosstab-level.
Tedious repetition is also required for each y-axis dimension member.

The crosstab in define view
UserPostedImage


The crosstab result
UserPostedImage

The two left most columns in the above screenshot (Close Income Transaction, Normal Transaction) are members of the y-axis dimension.
The other five columns are calculated in order to get, what I assume is, your decried result. The result is in the most right column.
Note: This post only shows how counting the Close Income Transaction column.

Walkthrough
countValueRow
The countValueRow checks if you are on row level and if true, counts the current row in the d1 column (the most left column in the screenshot).
Note: The count function returns 1, when then d1 value (the most left column in the screenshot) contains a value and 0, when NULL.
Note: Only the row level has a nonzero value.

countValueSubTotals and GrandTotal
In order to explain I will now use the terminology where the row is a child and the subtotal is the parent. The countValueRow aggregates all of its children and assigns this value to the parent.
In the countValueSubTotal1 columns, only the SubTotal1 level of the hierarchy has a nonzero value.
Knowing where the nonzero values are the countValueSubTotal1 is referenced (c1) repeating the same aggregate function in countValueSubTotal2.
The Close Income Transaction CountTotal column sums the c1-c4 temporary columns.

countValueRow
if allcount(d1, all(v,c), m1) = 0 then count(d1, 0, m1) else 0

countValueSubTotal1
sum(c1, all(c), m1)

countValueSubTotal2
sum(c2, all(c), m1)

countValueGrandTotal
sum(c3, all(c), m1)

Close Income Transaction CountTotal
sum(c1, 0, m1) + sum(c2, 0, m1) + sum(c3, 0, m1) + sum(c4, 0, m1)

Using colon : an alternate syntax is also valid and produces the same result (sum over c1 to c4, include both)
sum(c1:c4, 0, m1)

The TARGIT xview file can be found https://file.io/73j2xl

Edited by user Monday, November 14, 2016 5:11:54 PM(UTC)  | Reason: Not specified

Best regards

Páll, CTS
SO  
#10 Posted : Tuesday, November 15, 2016 2:49:31 PM(UTC)
SO
Rank: Advanced Member

Groups: extranet\Forum
Joined: 8/19/2014(UTC)
Posts: 88

Thanks a lot for the help. This seemed to work!

A lot (10 additional colums! + 2 additional for % view) for what seemed to be a quite simple mathematical job.
Users browsing this topic
Anonymous
You cannot post new topics in this forum.
You cannot reply to topics in this forum.
You cannot delete your posts in this forum.
You cannot edit your posts in this forum.
You cannot create polls in this forum.
You cannot vote in polls in this forum.

Notification

Icon
Error