Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have 2 sets of question and I would like to know your idea.
1. I have 2 pivot table, the 1st one was getting the sum of all sales.
ex:
Desc | Sales |
---|---|
Sample1 | 200 |
Sample2 | 300 |
Sample3 | 400 |
Total | 900 |
(this is just a sample, imagine it as a pivot table, my other dimension is month)
My second table compose of the percentage of the sales, it will depend on the total of the 1st table.
formula: sample1-> sales/total = percentage
Desc | Sales |
---|---|
Sample1 | 22.2 |
Sample2 | 33.3 |
Sample3 | 44.4 |
Total | 100% |
How can I produce this percentage if it is not joined to the 1st table?
2.) I have a date field and a list of customers and their item quantity.
Month | January 2012 | February 2012 | March 2012 |
---|---|---|---|
Customer Name | Qty | Qty | Qty |
CustNo | 5 | 0 | 0 |
CustNo2 | 12 | 15 | 100 |
CustNo3 | 90 | 20 | 0 |
CustNo4 | 22 | 30 | 23 |
CustNo5 | 0 | 0 | 0 |
I need to filter this data who doesn't have a quantity for the last n months.
eg: Filter all the Customers who doesn't have any quantity for the last 2 Months [Date selected is February 2012(Included)]
output: CustNo5, since CustNo5 doesn't have any quantity from February2012~January2012
Note: I only have one Date Field.
I also want to know if it is better if I do it on Scripting or in the Chart
Thanks in advance!
-Donna
I don´t understand the point 1 ... why do you need to separate the information in two tables?? Don´t they have the same dimensiones? You can use the option "Relative" in the options of the expressions sheet to get the percentages, else you need an expression using the same formula that table1 but adding sum(total Whatever) as denominator.
Question 2: I would do it on scripting if the rule is always for the last 2 months, if the rule depends on the selection of the user, it should be done in the chart
hope this helps.
Regards
Hi Donna,
I'm attaching a sample qvw I've created to check your item 2 requirement using set analysis. You can set the number of months for the calculation.
About item 2, I agree with Jose. Especially in cases when your calculation is complex, it is preferable to leave the heavy calculation "in the background" during the reload, so that the user experience is good when refreshing the charts.
Hope it helps you.
Fernando
Alright, Thanks. I will try this one