G’day, I have a view with 6 columns. First column displays the year(categorized). Second column is the Managers name (categorized). Third column is the clients names within that manager (categorized). Fourth column is YTDSales. Fifth column is the amount of a certain item from within each of those documents. The sixth column shows the percentage of that item to the YTDSales. Formula below:
Item := @Sum(@Transform(Lineitems; “x”; @If(@Begins(x; “BL”); @ToNumber(@Word(x; “ë”; 7)); 0)));
Answer := @ToNumber(Item) / @ToNumber(TotalAmount) * 100;
Answer
This works fine per document but it won’t give me an answer as per the categorized manager. The totals are there but when it comes to this formula it won’t calculate for the categorized line in the view.
Is it even possible?
Thanks for your help,
Kenny
Subject: Categorized column problem…
Views dont work like that. The categorized line will not contain anything other than the value of the categorization.
You could mock this up by having a separate form/document per manager. You would need some script happening somewhere that would update this document with the current YTD figures. Then you could include this document in the same view, with the same Manager name being categoriezed, but having this new document sort first. I would color-code it so you know you are looking at a summary document.
This is only brainstorming to try to help you figure another way to give the users what then need to see at the view level.
Good luck.
Subject: Categorized column problem…
I think (limited by knowledge) the only way you could proceed is to have create variables on your documents for the different item cases. Then have an agent that rolled up the numbers the different ways you want it to calculate the percentages. However, I’m not sure you could get the totals you’re looking for without actually coding it myself and seeing what happened (more time that I have at this point)
Then display those values as seperate columns on the views. You’d get something like this
. . Year. . Mgr . . Cust . . YTD . . Apples . . Oranges . . Pears . . Tomatoes . . %. . App% . . Org%. . Prs%. . Tom%
. . 2007
. . . . Manager 1
. . . . . . Customer 1
. . . . . . . . . . . . . . . . . . . . . 1000. . . . . . 200 . . . . . . . 200 . . . . .300 . . . . . . . . 300 . . . . 100. . . . 20. … . . 20. . . . 30. . . . 30
. . . . . . Customer 2
. . . . . . . . . . . . . . . . . . . . . 2000. . . . . .1000 . . . . . . 500 . . . . .200 . . . . . . . . 300 . . . . 100. . . . 50. … . . 25. . . . 10. . . . 15
Subject: RE: Categorized column problem…
Yep, that’s kinda what I thought. Thanks for the clarification guys. I appreciate it.
KB