How to restrict Month field in Set analysis using valuelist() field

[Lakshmanudui]

Hi All, 

I have created one field using valuelist(), like valuelist(Category1, Category2, Category3)

valuelist(Category1, Category2, Category3) this valuelist(), I am taking as Dimension.

I am using Set analysis like

Pick(Match(valuelist('Category1', 'Category2', 'Category3'),'Category1', 'Category2, 'Category3'),

Sum({<Month=>}Sales),Sum({<Month=>}Profit),Sum({<Month=>}Quantity))

I am trying to restrict Month selection. but month selection is not reflected.

Because i am using valuelist(Category1, Category2, Category3).

Can you please suggest me how to restrict Month field using Valuelist() in Set Analysis.

Thanks & Regards,

Lakshman

[marcus_sommer]

Just remove {<Month=>} from the expressions to make it responsive again against any month-selection.

- Marcus

[Lakshmanudui]

Hi Marcus,

Thanks for your replay.

If I removed Month in Set analysis then if I select Month like Jan it will show Jan Month Data only.

But My requirement is If I select Month or  not selected in Month selections in filter always showing all months data.

That's what I mentioned {<Month=>},  If i select Month as Jan, But it will show only Jan Month data only and other months showing Null values.

Can you please suggest me how to do if i select month selection then it Won't reflected in data in Pivot table.

Thanks & Regards,

Lakshman

[marcus_sommer]

It's not really clear for me what do you want to achieve - the expressions should react on month-selections or not?

Beside this the above set analysis will only react on Month - any other period-fields aren't included and also not further fields.

- Marcus

[Lakshmanudui]

Hi Marcus,

Thanks for your replay.

I have attached example file.

Here i have taken below expression.

=Pick(Match(Valuelist('Category1','Category2'),'Category1','Category2'),

Sum({<Month>}Profit),Sum({<Month=>}Quantity))

My Requirement is if i select any month then Pivot table won't reflected.

But If i select Month selection Like Jan then Jan data only able to see and remaining data showing nulls.

Please see the attached qvw file for reference.

Can you Suggest me how to restrict month selection in Pivot table using pick(Match()) function in expression.

Thanks & Regards,

Lakshman

[marcus_sommer]

Go into the object-properties in tab presentation and in the mid of the left-side is an option to hide null which is usually enabled - disable it and try it again.

- Marcus

[Lakshmanudui]

Hi Marcus,

Thanks for your replay.

My Requirement is Month selection is not reflected in Pivot table.

For example If I don't want to select month selection then Pivot table showing all months data.

Same way If I select Month selection also I want to show all Months data in Pivot table.

But If I select any Month then based on the Month selection data will display in Pivot table.

For example, If i select Jan then Pivot table showing Jan Month data only.

Please see the below attached file.

Can you please suggest me If I select any month then in pivot table showing all months data.

Thanks & Regards,

Lakshman

[marcus_sommer]

Sorry, I don't understand what do you want to achieve. The object should react on a selection or not?

Most common is that objects react to the selections and that excluded data and/or missing ones won't be displayed - just this what exists is there. Many other views which ignore or overwrite selections and/or are added to show missing data are often possible but have usually significantly disadvantages in regard to the complexity, the performance and the usability (is it really obvious for an user if objects didn't react on selections ?).

Beside this you may consider a general re-design of your application because synthetic dimensions should be rather avoided. In your case this might be done with a transforming of your crosstable measures into  a data-stream logic - means there are just two fields - one KPI field with 'Profit', 'Sales', 'Quantity' as values and a Value field with the numbers. The KPI could be used as dimension within the objects and/or as a criteria within a set analysis.

- Marcus