Discussions
Categories
Choose a Product
THRUST SERVICES
CORE APPS
CE PRODUCTS
...
Quick Links
POPULAR
HELPFUL TIPS
Groups
My Links
FOR SIGNED IN MEMBERS:
Back to website
Home
Designing Analytics Reports
How can I multiply DS row x value in another DS or global variable
Migrateduser
<p>I have a programming challenge I cannot solve. I am hoping to get help from the experts.</p><p> </p><p>I have one SQL Server dataset with <strong>sales results</strong> in which I use <span class='bbc_underline'>Case statements</span> to get monthly column totals.</p><p>I have a second dataset of <strong>sales objectives</strong>, also created with case statements. I join them together in a JOIN dataset and I get columns in one table:</p><p> </p><p>JAN Sales JAN Quota JAN %</p><p> 90 100 90%</p><p> </p><p>The challenge is I need to pro-rate the quota, for example only apply 50% of the quota on day 11 of a month with 22 sales days. I have a third dataset that tells me the number of days elapsed .. and percentage to apply, but it is in a <span class='bbc_underline'>Flat File</span> and I cannot use case statements.<br />
<br />
How can I do something like the following?</p><p> </p><p> </p><p>JAN Sales JAN Quota JAN Elapsed Prorated Quota JAN %</p><p>90 100 X 50% = 50 = 180%</p><p> </p><p>Since the Flat file cannot be queried with case statements, I cannot join this dataset to the results. I was thinking there must be a way to multiply datarows x the value in other datarows .. or perhaps set it to some global variable so it can be used in the aggregate in the table. Any help would be appreciated.</p><p> </p><p> </p><p> </p>
Find more posts tagged with
Comments
There are no comments yet