[Solved] Standard Filter data executed with Form Control box

[Kalculon]

Hello,

I need some advice in a chart I'm designing... I'm including it as an attachment.

The spreadsheet only uses two tabs: The 'Data' tab is used for Sales/Taxes/Service Fee info and 'Chart' reflects the respective stacked data and sum for Sales.

What I've done is to use Forms Control Check Boxes to activate or deactivate my data columns and then have it reflected in my chart automatically; this part works fine.

The part I'm having a bear of a time trying to figure out is how to pass the selected Start and End dates back to the 'Data' tab's Standard Filter, so that only the desired time-frame data is reflected in the chart automatically or even semi-automatically by clicking the Update Graph Button.

I would of course like to have a logical catch filter on the End Date so that it can never be before the Start date as well but even if this can't be solved easily, having the chart update on-the-fly is my main goal at this point.

I'm at a loss how to approach this problem and any help would really be appreciated.

Rob

[Villeroy]

viewtopic.php?t=88516&p=416210#p416210

[Kalculon]

Thanks for your post Villeroy. This doesn't seem to work for me; I've enabled macros but there are no pull down items in order to select and filter. I will keep playing with it. There is no easy way to import my selected dates into the filter and then re-query? Was hoping there was a simple way of resolving this but it appears this is not the case.

[Villeroy]

This is the most easy and flexible way, particularly because it does not depend on any macros.
Did you register the database document? menu:Tools>Options>Base>Databases. Add the downloaded PowerFilter.odb. Then load my spreadsheet.

Store categorised data in a database.
Use a small input form to store filter criteria in the same database. The "Filter" table in my example db stores filter criteria.
Create a query to get row sets with calculated data based on the entered criteria (a query is a "formula" which returns a whole table rather than one value).
Database row sets are always normalised. To create a not normalised cross table with one column per category for your chart, you can use a pivot table.

####################### HERE WE GO AGAIN #######################
Open my PowerFIlter.odb you have registered to work with stand-alone documents. We are going to modify it a little by mere copy&paste.
Go to the "Tables" sections and call menu:Tools>SQL...
Paste the following SQL script and execute it. It expands the "Filter" table by 3 columns and one row.

Code: Select all

ALTER TABLE "Filter" ADD COLUMN BOOL1 BOOLEAN;
ALTER TABLE "Filter" ADD COLUMN BOOL2 BOOLEAN;
ALTER TABLE "Filter" ADD COLUMN BOOL3 BOOLEAN;
INSERT INTO "Filter" VALUES(3, Null, Null, Null, Null,'Chart Filter', True,True,True);

After getting a success message, close that dialog and call menu:View>Refresh Tables which notifies Base that the structure of the database has been changed by a command line tool.
-------------------------------------------------------------------------------------------------------------
menu:Insert>Query(SQL View) and paste the following SQL:

Code: Select all

SELECT YEAR( "Data"."D" ) ||'-'|| MONTH( "Data"."D" ) AS "Month", 
  CASEWHEN( "Filter"."BOOL1", SUM( "Data"."V" ), NULL ) AS "Pre-Tax",
  SUM( "Data"."V" ) * CASEWHEN( "Filter"."BOOL2", - 0.30, NULL ) AS "Taxes",
  SUM( "Data"."V" ) * CASEWHEN( "Filter"."BOOL3", - 0.20, NULL ) AS "Service Fees" 
FROM "Data", "Filter" 
WHERE "Filter"."FID" = 3
  AND ( "Filter"."D1" <= "Data"."D" OR "Filter"."D1" IS NULL )
  AND ( "Filter"."D2" >= "Data"."D" OR "Filter"."D2" IS NULL )
  AND ( "Filter"."INT2" <= "Data"."CID" OR "Filter"."INT2" IS NULL ) 
GROUP BY YEAR( "Data"."D" ), MONTH( "Data"."D" ) 
ORDER BY YEAR( "Data"."D" ), MONTH( "Data"."D" )

Save the new query as qChartFilter
Save and close the database.
-------------------------------------------------------------------------------------------------------------
Open the here attached document PowerFilter2.ods and try the red spreadsheet tab "Topic_89590".
Like the other filter forms, this one involves a one-line macro to refresh the import range after you finished criteria input.
I did not add a chart yet. But if you do so, you will notice that the chart range expands and shrinks automatically with the import range after you selected more or less months to display. Together with expanding calculation formulas, this is another thing you don't have to bother when using databases.

[Villeroy]

[deleted previous answer about hiding columns because you don't need to hide any columns. your check boxes do the right thing already]
I added a very old but versatile filter macro to hide rows by means of an "advanced filter" (filter by criteria in cells).
I simplified your formulas a little bit and replaced the useless date controls with validated cell input.
Nevertheless, I'd always do such things with a database because the storage is more consistent and you avoid many problems with growing data sets.

[Kalculon]

Thank you Villeroy. I'm currently recovering post-op and will review all that you've posted shortly; this last chart with the included macro seems to be exactly what I needed.

[Villeroy]

I forgot another Basic snippet which writes date control values to spreadsheet cells: viewtopic.php?f=21&t=18714

[Kalculon]

Villeroy,

Just wanted to thank you again. I'm going to try incorporating the macro and changes into my spreadsheet; you've helped a great deal! If there are any questions I'll post them as need be.