You’ll need 2 calendar components for this, or 2 additional selectors. You’ll end up with 4 selections which will be used to update your gauge value.
The date insert from the calenders will be serial numbers though, so you’ll need to convert these.
If youre using connections to return the relevant value(s) for the selections it will be a lot easier than trying do the manipulation in the Excel model.
Where are the dates coming from? is this an additional column to add to your data?
As I mentioned, its easier to do this if youre using connected data, as all you need to do is pass the 4 input values to return the quantity value.
If all this is static in the Excel model then you’ll need to either need to use 2 combos based on the same date range to capture the 2 values or just use 2 calendar components instead. Either way you’ll probably need to use the offset function to get the quantity for the date range.
If this is all in Excel provide some more data (with the dates) and I’ll see if I can give you an example.
Dates are coming from db only. I attached one sample file for your reference. Background color of Date column is green (Remove the column and run the report). I used Filter component to filter the Gauge.
I need that Date field in Filter component as a dropdown Calender.
I will use that Date field before my measure column (last column) in source data.
I am not sure, Can I use multiple Combobox (i.e Combo1->Region, Combo2->State and Calender control to provide Date ranges.) to filter a Gauge to fullfill my requirements.
For Date value
Actually for Date value, In my report, 1 text box and a button will use,
when user click on the button Calender will display, select the value and value will be populated in text box and calender will disappear.
2 same property will use for Start Date and End Date.
Data filter thru combobox related issues is solved. Now I have a new query.
To generate Dashboard report I have a Excel file as Data source. I cant provide cell ranges to generate the report. Range should be dynamic. How I can implement this with dynamic data.
Do you mean you cant bind the components to a specific range as the Excel data isn’t consistent?
If youre getting additional rows then just expand the ranges (and ignore blank cells). If you’re getting additional columns which affects the current bindings then thats a problem.
I was asking for, right now I am working with sample data prepared in xcelsius worksheet only. When I will go live, I will get thousands of rows. How I will interect with that source file.
For Demonstration purpose I will go live later, before that I wants to implement the required functionality.
I wants to click a button (Toggle/Push ?) to open calender component, choose date, date is updated in text box and calender will disappear on double click or mouse move out from calender and repeat the same
I dont have Interective calender component. How to develop the same functionality using Calender component. I wants to bind selected date from calender component with my source data.
I have Year and Custom Date both in parameter. Can I Disable custom date controls, once I select Year or My Custom dates are bind with Year value, should not allow beyond the year, I selected.
I would suggest, considering that you may be working with thousands of rows, using connections (qaaws / liveoffice) for this.
One way of doing it would be to pass the dates from your calendar components as input values into the query to return only the relevant data.
In response to your other questions:
Yes that can be done using either a push or toggle using DV (dynamic visibility). Although I think you need 2 Calendars, 1 for start date, and 1 for end date.
You can bind the selected date to a cell which is used as an input value on your connection (if you create one). If using only static Excel data then you’ll need to use the calendar date as a lookup against your data.
I’m assuming here that you need to need to limit the date ranges on the calendar based upon a year. This can be done by using the calendar limits on the component, and bind these to cells. For example if you had a combo box containing years, the selected item from this would use the same cell as the calendar limit years.
Using dynamic visibility it is working fine, but having 1 prob, when Again I am clicking on the button calender not appearing, Hope now I can implement the same.
Yes I need two calender components for Start & End date.
Right now I am concentrate on static data only, so really it will work nice and I will implement the same.
But I will come to you again for dynamic data.
Yes I will use the calender limit option. Its great. At present my doubts are clear. Will come soon…
I have a set of data which is sorted by Location / Product/ Year.
I wants to display year wise Total. But my Year is not in sorting order (1).
Is there any function available to do the same.
Actually my requirement is When I will open DashBoard irrespective of any filter Current Year Data will show. I can handle my requirements now, as you guide me a lot, but facing problem due to data sorting issue.
Theres no easy way to sort data within Xcelsius unfortunately, however if you just want the default combo selection to be the current year you can use the dynamic selected item (combo properties\behaviour\common) and bind this to a cell that contains the current year, with the formula =YEAR(TODAY()).
If you are looking to access large amounts of data (1000’s of rows), please be aware that Xcelsius is more of a data visualization tool.
Some solutions
Use QaaWS/Live Office with prompts, brings small blocks of data into Xcelsius
BI Web Services, alternative to Live Office in 3.1 SP2 and above
XWIS from Antivis (http://www.antivia.com) - Connectvity/Productivity tool for Xcelsius that will save you alot of hard work and can handle tens of thousands of rows of data making you dashboard development alot easier.
I will Select 2 Dates from calender control and In between 2 Data ranges data needs to show in report as a Average value. I tried it with AVERAGEIF, but Xcelsius doesnot support AVERAGEIF. Its working well with SUMIF. Is there any option to do the same.
(BOB member since 2002-08-19)