-
1. Re: Blended data sets not resulting in desired solution
Donna ColesJan 22, 2019 12:35 AM (in response to Alex Martino)
Hi Alex
If you remove the link to blend on month you should get your orders column to contain the same number 84399 throughout, and your 'order per' calc will re-adjust. See the attached I set up in v 2018.1. 1 sheet has the data with the blend, the other without
You will get a warning message but the values still display
No blend link
blend on month, not all categories have expenses in a month
When you say you want to do this on a monthly level too, if you're still looking to divide by total sales for each month (ie 84399), then you can again just remove the blend link on month
in the example below, you can see Feb has no expenses for Penciles, so no row is shown
If this isn't what you're after, then please provide a packaged workbook with a simplified example in it (like I've provided) and more detail on exactly what numbers you're expecting when.
Packaged workbook with above examples attached (v2018.1)
Many thanks
Donna
-
ForumBlendQ.twbx 82.8 KB
-
-
2. Re: Blended data sets not resulting in desired solution
Alex Martino Jan 22, 2019 10:54 AM (in response to Alex Martino)Hi Donna,
The first example works but the second example doesn't. I want the expenses for that month divided by the sales for that month (not the total sales)
-
3. Re: Blended data sets not resulting in desired solution
Donna ColesJan 24, 2019 12:45 AM (in response to Alex Martino)
I wondered if that was the case, but wasn't sure since you stated that your expenses doesn't always have a value for every category in the month, so the value in this instance I guess is 0....
FIrst off, using my example, if you apply the blend link on month and then select the date pill to 'show missing values', you'll see the missing rows displayed and the amount of sales based on sales in the month
As there is 'missing data' for this row, as opposed to the row existing with a NULL value, then the data displayed is just 'empty'.
If you want to get 0 displayed, you'll need to do the following :
Create a new field based on the Expenses : Amount field
ZN(LOOKUP(SUM([Amount]),0))
and then base your monthly category per sales calc on that field instead.
This is basically using a table calculation to 'look up itself' and replace with 0 in the event a value doesn't exist.
Updated workbook v 2018.1 attached
Donna
-
ForumBlendQ.twbx 124.2 KB
-
-
4. Re: Blended data sets not resulting in desired solution
Alex Martino Jan 25, 2019 9:27 PM (in response to Alex Martino)Actually now that I read over your post, I think this works. The blend works for the monthly information and the non-blend allows me to get the overall average by category. I don't need to see if there are 0 expenses. Thanks!
-
5. Re: Blended data sets not resulting in desired solution
Alex Martino Jan 25, 2019 9:34 PM (in response to Alex Martino)That was fantastic actually.
-
6. Re: Blended data sets not resulting in desired solution
Donna ColesJan 29, 2019 12:45 AM (in response to Alex Martino)
No worries - glad it helped.
Donna