How to fix a dynamic drop down or check box filter

[gurusai sankar]

Hi All,

I have the data i.e proposal cost, project cost, bugdet cost, actual cost and forecast. each cost have cap and exp cost category.

As per my requirement-

i am showing all these Sum of costs in combination bar chart with Year/Quarter wise. For this i have created a drop down filter by using property control (fixed values). Drop down values are Total , Cap, Exp . (Total = cap + exp) . This filter is aplied for this graph aswell as cross tables which are linked with this graph.

similarly i am also showing cumulative cost on sepaerate bar graph.For this i have created a seperate drop down filter by using property control (fixed values). Drop down values are Total , Cap, Exp . (Total = cap + exp). This filter is applied only for this graph.

Now my requirement changed, I should create a check box or drop down filter, by deaflaut it should show all the cost except forecast, if users wants to see the forecast along with all the cost then only it should show. i was also used forecast in above two drop down filters in each section (Total, Cap,Exp), Now i should give an option to the user that by deafult forecast it should not show, only should show rest all costs except forecat ,incase if user wants and check a check box or drop down filter then only forecast display along with all the costs in graph and cross tables.

PFA- Attached Dxp file.

Can someone help on this

 

Regards,

Guru

[Fabian Duerr]

I'm not sure if understood correctly without seeing any data... How is the structure of your data Have you tried to work with a filtering scheme Or with the option to limit data by expression

[Julie Sebby]

Can you add some screenshots of your existing property controls and setup

[gurusai sankar]

Hello Jsebby, Thanks for your reply. I have created a drop down filter by using property control fixed values and applied this filter to the graph and cross tables. it is working fine dynamically however we want.. my drop down vallues are like in the below (Total, Cap, Exp)

 

Total: sum(proposal cost),  sum(project cost), sum(bugdet cost), sum(actual cost), sum(forecast)

 

CAP: sum(Cap proposal cost),  sum(Cap project cost), sum( Cap bugdet cost), sum(Cap actual cost), sum(Cap forecast)

 

Exp: sum(Exp proposal cost),  sum(Exp project cost), sum( Exp bugdet cost), sum(Exp actual cost), sum(Exp forecast)

 

My requirement is Now: Actually forecost is used in above drop down filter (Total, Cap, exp). Now We should create a check box or dropdown filter that we should not show forecast values bydeaflaut in the graph and cross tables., we have to show rest all cost except forecast, whenever user will select a check box or drop down filter which we need to create, then only forecast will show along with all cost. 

 

How should i achive this

 

Regards,

 

Guru. 

[gurusai sankar]

Hello fadb, Thanks for your reply. I have created a drop down filter by using property control fixed values and applied this filter to the graph and cross tables. it is working fine dynamically however we want.. my drop down vallues are like in the below (Total, Cap, Exp)

Total: sum(proposal cost),  sum(project cost), sum(bugdet cost), sum(actual cost), sum(forecast)

CAP: sum(Cap proposal cost),  sum(Cap project cost), sum( Cap bugdet cost), sum(Cap actual cost), sum(Cap forecast)

Exp: sum(Exp proposal cost),  sum(Exp project cost), sum( Exp bugdet cost), sum(Exp actual cost), sum(Exp forecast)

My requirement is Now: Actually forecost is used in above drop down filter (Total, Cap, exp). Now We should create a check box or dropdown filter that we should not show forecast values bydeaflaut in the graph and cross tables., we have to show rest all cost except forecast, whenever user will select a check box or drop down filter which we need to create, then only forecast will show along with all cost. 

How should i achive this

Regards,

Guru. 

[Fabian Duerr]

Guru, I understand what you are trying to do. But I would need to have a look at your data (structure). Can you provide a dxp file It doesn't need to be the original data. But same structure and property control filters. Then I can have a closer look.

[Fabian Duerr]

Guru, thank you for the dxp file.

 

Currently your visualization is not doing what it is supposed to do. It always sows the sum over all years no matter what year you choose. The data does not match your cross table. Please check!

 

Your data format is not really suitable for what you want to do. Life get's simple when you unpivot your data table. Because then you can do simple filtering. Please have a look at the attached file. (Actually first I needed to do a pivot to get rid of the useless Category column (you have al infos in column names) and then I did an unpivot over all columns)

 

If you want to filter on all CAP or all EXP then you can create a helper column. It's called 'Category (2)' in my dxp file. Filter on CAP and EXP to get TOTAL.

 

Just add another helper column to quickly filter out all un-wanted forecast columns.

 

Please let me know if this helps to solve your problem.

 

 

[Fabian Duerr]

Guru, modified dxp file attached. Please let me know if this works for you!

[gurusai sankar]

Hi Fabd,

 

Thanks for your help and modified the Dxp. But still i am unable to understand how to include and exclude forecast dynamically Can't we add a check box or dropdown to include and exlcude forecast values whenever required. (By keeping my dropdown filter however i am showing) 

 

 

 

Regards,

 

Guru

[Fabian Duerr]

The idea was to work with filters instead of drop downs and document properties. I have attached a nwe dxp to my last answer. It might not be 100% the solution you prefer but it should be close.

 

The user can use the checkboxes to see cap or exp or both (=total) and/or forecast

 

[Fabian Duerr]

You can also still create new doc properties for example with the option 'unique values from column'. Let's call the prop for the year column 'myyear' and the unique values will be 2017,2018.... And then you can use this doc prop to limit the data in your visualization. The expression then would be: [Year] = ${myyear}. So only data will be shown where this evaluates to TRUE