How do i write a Variable which allows me to select more than 1 week to sum up the sales. Currently I have created a variable and used it in the chart which allows me to input a number or select from drop down which calculates the sales, however I want to create a variable where i select 3 weeks, it should sum up the sales for 3 weeks instead of 1 week.
You are absolutely right, But the problem I have is, still we dont know how many weeks are they going to select for each criteria and its the average, and just under my chart I have a Pivot which exactly shows all the numbers from the chart and now they want another column added to the Pivot under this to show the calculation as I mentioned.. I tried, tried and tried and finally got it working by hard coding the expression. I tried the straight table at one point and even different expression in pivot as well, but its just messed up when I move the dimensions within the pivot and further to my plight I even have Various groups at the Product level and even for Measures...So I have huge data I am working with now :-). For now it looks like its fine, but I was thinking of what you mentioned again and want to review as per your comments when it comes to performance of the application. I havent gone that far yet though.
Thank you once again for the quick response Tanel.
btw I didn't think about it at first, but the calculation "(post - pre) / 100" doesn't actually return any meaningful percentage measure. It returns variance (amount) of monthly average gross sales in hundreds. Doesn't make sense to show amount as percent.
Variance in percent you already have under "Result" (=post/pre-1).
I am back again and happy to say that I fixed all the old issues so far. It looks like you are also working on a sales related project. May be you have come across a requirement I have. I have a chart which shows the sales VS Orders data. The requirement is that at any point this should show the data for Periods (similar to Months) in the current year comapred to same Periods for previous year and should not change with the selections anywhere. Eg: We say we are in Period 3 now, then the chart should show something like periods 1,2 for 2011 and 1,2 for 2010 as Period 3 is not finished yet. However after period 3 it should be 1,2,3 for 2011 and 1,2,3 for 2010. Can you please help me with one? I am sure you can 🙂
This is possible to solve with Set Analysis. There are plenty of examples here in forum and QV help file is also worth to study. Anyway, you could start testing with something like this:
Current year: sum( {1< Year = {$(=Year(today()))}, Month={"<$(#=month(today()))"} >} Sales) Previous year: sum( {1< Year = {$(=Year(today())-1)}, Month={"<$(#=month(today()))"} >} Sales)
Here any selections are discarded by "{1<" then Year and Month field selections are explicitly defined.
This looks great, Thanks Tanel will get back to you with the results and I have looked at the samples, but couldnt relate to my requirement... anyways will try more.
