for each product i need to display the num_of_product sold on a given date. User can choose 3 dates as shown above. This i am currently able to acheive using crosstab. But the other requirement is i need to come up with 2more columns to the right side of the cross tab and display the different between the product sales on (date1 and date2) and (date1 and date3).
Can anyone tell me how to do this with help of a formula or a variable?
since the user can choose 3 dates randomly from Database, i need to sort the dates in ascending order from left to right. (date1 is minimum, date2 is median and date3 will be maximum). any input is highly appreciated.
It depends on how the three dates are being input by the user.
In case you have three different prompts the solution is clear.
But if you have one prompt which receives the list of dates (no idea on how you can be sure that there are exactly three dates in this case) it get a bit more tricky.
thank you very much for the solution, that helped me to solve 50% of my problem. Now I am stuck in getting the middle date from the user input. The sample logic that I got from you ( ie… D2 not in (D1, D3))
is not working fine as we dont have “not in” option in reports. ( I am not sure if this can be achieved by other means).
So everytime i try to find the middle date, i am getting MULTIVALUE and not as a unique value like what we get in Min() or MAX (). I tried many other options like “previous()” and “Between (;” but they all give me multivalue and i am not able to use that Multivalue middle date for calculations.
It would be great if someone can suggest a way to retrieve the Distinct Middle date value from user input and store it in a 3rd variable.
I have a similar issue, when using Month number. I have to calculate the difference in counts between two months. I tried the above using Min and Max but still get both count totals? Any suggestions.
Use different prompt text for the 2 dates, and then store the results of the prompts in 2 different variables. If you get a multi-value error, try using either sum or foreach in your formula. (For the question above, possibly using ForEach([Product]) might have solved the multi-value error problem.)
(BOB member since 2009-08-17)
(BOB member since 2005-03-14)
(BOB member since 2009-09-29)
(BOB member since 2008-12-02)