sort of lookup/measure question!!!

masplin

Active Member
Joined
May 10, 2010
Messages
406
Ok so here are the 3 powerpivot tables. Transaction month and Vistor month are related. user ID is related between transactions table and users table. Apolgise for rubbish formatting.






















<table border="1" bordercolor="#999999" cellspacing="0"> <tbody><tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF">Transaction </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="84" bgcolor="#FFFFFF">User ID </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF">Transaction </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF">transaction month </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">133 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Add </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">143 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Add </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">133 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Cashout Fee </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">133 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Cashout </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">143 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Cashout Fee </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">143 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Cashout </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">153 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Add </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="center" width="84" bgcolor="#FFFFFF">153 </td><td rowspan="1" colspan="1" align="left" height="27" valign="center" width="127.5" bgcolor="#FFFFFF">Cashout Fee </td><td rowspan="1" colspan="1" align="left" height="27" valign="center" width="133.5" bgcolor="#FFFFFF">Jan-11 </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="84" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="127.5" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF">Users </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="84" bgcolor="#FFFFFF">User ID </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="center" width="127.5" bgcolor="#FFFFFF">Reg Month </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">133 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF">Jan-11 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">143 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF">Jan-11 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="center" width="84" bgcolor="#FFFFFF">153 </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF">Jan-11 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="center" width="84" bgcolor="#FFFFFF">163 </td><td rowspan="1" colspan="1" align="left" height="27" valign="bottom" width="127.5" bgcolor="#FFFFFF">Jan-11 </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="84" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="127.5" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF">WebStats </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="84" bgcolor="#FFFFFF">Visit Month </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF">New Visitors </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="84" bgcolor="#FFFFFF">Jan-11 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF"> 10,000 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="left" height="25.5" valign="bottom" width="84" bgcolor="#FFFFFF">Feb-11 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="127.5" bgcolor="#FFFFFF"> 12,000 </td><td rowspan="1" colspan="1" align="right" height="25.5" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> <tr><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="93" bgcolor="#FFFFFF"> </td><td rowspan="1" colspan="1" align="left" height="27" valign="bottom" width="84" bgcolor="#FFFFFF">Mar-11 </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="127.5" bgcolor="#FFFFFF"> 14,000 </td><td rowspan="1" colspan="1" align="right" height="27" valign="bottom" width="133.5" bgcolor="#FFFFFF"> </td></tr> </tbody></table>
 

masplin

Active Member
Joined
May 10, 2010
Messages
406
and this is the pivot table I am aiming for. I am using "visit month" here as per your suggestion as works to pick up the visitor numbers form the webstats table. New loads is a measure counting transactions in the transaction table. Registrations woudl be a count of User ID in Users table where Reg Month = Visitor month.


<table width="461" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:4010;width:71pt" width="94"> <col style="mso-width-source:userset;mso-width-alt:2944;width:52pt" width="69"> <col style="width:42pt" width="56"> <col style="mso-width-source:userset;mso-width-alt:2645;width:47pt" width="62"> <col style="mso-width-source:userset;mso-width-alt:3584;width:63pt" width="84"> <col style="mso-width-source:userset;mso-width-alt:4096;width:72pt" width="96"> </colgroup><tbody><tr style="mso-height-source:userset;height:24.75pt" height="33"> <td class="xl157" style="height:24.75pt;width:71pt; font-size:10.0pt;color:white;font-weight:400;text-decoration:none;text-underline-style: none;text-line-through:none;font-family:"Arial Narrow";border-top:none; border-right:none;border-bottom:.5pt solid #DCE6F1;border-left:none; background:#366092;mso-pattern:#366092 none" height="33" width="94">Transaction Month</td> <td class="xl158" style="width:52pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:none;border-right:none;border-bottom: .5pt solid #B8CCE4;border-left:none;background:#366092;mso-pattern:#366092 none" width="69">New Loads</td> <td class="xl158" style="width:42pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:none;border-right:none;border-bottom: .5pt solid #B8CCE4;border-left:none;background:#366092;mso-pattern:#366092 none" width="56">New Visitors</td> <td class="xl158" style="width:47pt;font-size:10.0pt;color:white; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:none;border-right:none;border-bottom: .5pt solid #B8CCE4;border-left:none;background:#366092;mso-pattern:#366092 none" width="62">New Load Conv%</td> <td class="xl157" style="width:63pt" width="84">Registrations</td> <td class="xl157" style="width:72pt" width="96">Conversion %</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl159" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right: none;border-bottom:.5pt solid #DCE6F1;border-left:none" height="17">January 2011</td> <td class="xl160" style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092"> 24 </td> <td class="xl162" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">16,298</td> <td class="xl163" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">0.1 %</td> <td class="xl157" align="right">500</td> <td class="xl164" align="right">3%</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl159" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right: none;border-bottom:.5pt solid #DCE6F1;border-left:none" height="17">February 2011</td> <td class="xl160" style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092"> 144 </td> <td class="xl162" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">21,986</td> <td class="xl163" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">0.7 %</td> <td class="xl157">
</td> <td class="xl157">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl159" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right: none;border-bottom:.5pt solid #DCE6F1;border-left:none" height="17">March 2011</td> <td class="xl160" style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092"> 317 </td> <td class="xl162" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">31,808</td> <td class="xl163" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">1.0 %</td> <td class="xl157">
</td> <td class="xl157">
</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl159" style="height:12.75pt;font-size:10.0pt;color:black; font-weight:400;text-decoration:none;text-underline-style:none;text-line-through: none;font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right: none;border-bottom:.5pt solid #DCE6F1;border-left:none" height="17">April 2011</td> <td class="xl160" style="font-size:10.0pt;color:black;font-weight:400; text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092"> 318 </td> <td class="xl162" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:none; border-bottom:.5pt solid #DCE6F1;border-left:none" align="right">16,908</td> <td class="xl163" style="font-size:10.0pt;color:black;font-weight: 400;text-decoration:none;text-underline-style:none;text-line-through:none; font-family:"Arial Narrow";border-top:.5pt solid #DCE6F1;border-right:.5pt solid #366092; border-bottom:.5pt solid #DCE6F1;border-left:.5pt solid #366092" align="right">1.9 %</td> <td class="xl157">
</td> <td class="xl157">
</td> </tr> </tbody></table>
Hope I'm making more sense. Thnaks
 

ruve1k

Board Regular
Joined
Aug 31, 2008
Messages
171
Now I can't create a relationship between Reg Month and Visit Month as visits already related to Trans Month.
I think that was only in v1. In the current version you can have two relationships mapping to Visit Month. So you'll have a many-to-one relationship from Transaction[Trans Month] to Webstats[Visit Month] and another many-to-one relationship from Users[Reg Month] to Webstats[Visit Month].
Essentially, Webstats will act as a common Dim table for the two fact tables: Transaction & Users. Then Registrations would be a simple count and would reflect the month context.
 

masplin

Active Member
Joined
May 10, 2010
Messages
406
Ah Ok so have related "visitor month" to "Reg month". However if I just add a count of User ID from the User table I get the same number in every row which is the total of all User ID i.e. it is ignoring the month filter in rows.
 

masplin

Active Member
Joined
May 10, 2010
Messages
406
Actually you can't have many to one table relationships. the relationship failed with this error

"You cannot activate the relationship because a set of active relationships already exists between tables transactions and WebStats."
 

masplin

Active Member
Joined
May 10, 2010
Messages
406
I'm using 2012 downloaded about a month ago. If I try to create the Users[reg month] - webstats[visit month] relationship i get a dotted line in the diagram. If i go to manage relationships it looks like this

<table><thead><tr><th>Active</th><th>Table</th><th>Related Lookup Table</th></tr></thead><tbody><tr><td>Yes</td><td>transactions [User ID]</td><td>users [User ID]</td></tr><tr><td>Yes</td><td>transactions [Gym ID]</td><td>gyms [Gym ID]</td></tr><tr><td>Yes</td><td>transactions [Product type]</td><td>TheorComm [Product Type]</td></tr><tr><td>Yes</td><td>transactions [Transaction Month]</td><td>WebStats [Visit Month]</td></tr><tr><td>Yes</td><td>users [Registration voucher code]</td><td>voucher codes [voucher Code]</td></tr><tr><td>Yes</td><td>users [City Code]</td><td>PostCodes [City Code]</td></tr><tr><td>No</td><td>users [Reg Month]</td><td>WebStats [Visit Month]</td></tr><tr><td>Yes</td><td>gyms [Gym City Code]</td><td>GymPostCode [City Code]</td></tr><tr><td>Yes</td><td>products [Gym ID]</td><td>gyms [Gym ID]</td></tr></tbody></table>
So shows a "No". If I double click and check "activate" it give me that error. Coudl it be because there are other relationships. i'll try creating a new workbook with those 3 simple table and see if I can get what you get.
 

masplin

Active Member
Joined
May 10, 2010
Messages
406
So on a simple workbook with just the tables I pasted in it works fine. so something about my real tables that it doesn't like.
 

masplin

Active Member
Joined
May 10, 2010
Messages
406
If I delete the transaction=webstat relationship i can create the user-webstats. Oddly I then try to recreate the transaction-webstats it fails but again saying

"You cannot activate the relationship because a set of active relationships already exists between tables transactions and WebStats."

However there are no other relationships between these 2 tables if this one is inactive!!!!
 

masplin

Active Member
Joined
May 10, 2010
Messages
406
Well I have found the problem. I have a relationship between transaction[user id] and users[user id]. if i delete this I can then create the 2 monthly relationships. However that knackers an even bigger portion of my workbook.

I deleted the user ID relationship, create the month relationship and tried to recreate the User ID relationship, but that fails with exactly the same error message as before about relationship exisitng between transaction and webstats tables.

I have no clue why what seems ot be an independent relationship is preventing the month relationships being set up so any advice appreciated.

Mike
 

Forum statistics

Threads
1,085,538
Messages
5,384,307
Members
401,887
Latest member
Somesh

Some videos you may like

This Week's Hot Topics

Top