set analysis feature

[#1]

Hi All,

I have the requirement.

In my set analysis expression, I want my field/filters to come from a excel sheet which users can change and they reflect when we reload the qvw document.

For example

Field is

CategoryName

Beverages

Condiments

Dairy product

Meat product

Seafood

In one of the graph my current set analysis expression is like this.

sum({<CategoryName = {'Beverages', 'Condiments'}>}SellingPrice)

Now if there is change in requirement and user want to see

Beverages +Condiments +Dairy product.

Hence, I don't want to hard code in my set analysis.

One Solution could be defining them in variable

vselect = 'Beverages', 'Condiments'

and then my set analysis will look like

sum({<CategoryName = {$(vselect)}>}SellingPrice)

It works fine but again its hard coding in the UI.

What I am looking for is to have an excel sheet defined where it has one column and has the CategoryName field.

I have created a separate table but my question is how should I associate it with the datamodel. Like how can selecting in that separate table field will make change in whole

Does anyone know of work around?

Thanks for the help.

Max

[sunny_talwar]

You can do something like this:

ConcatList:

LOAD Concat(DISTINCT List, Chr(39) & ', ' Chr(39)) as ConcatList

FROM yourExcel;

LET vList = Chr(39) & Peek('ConcatList') & Chr(39);

now use vList in your expression's set analysis.

[#3]

As you said, the filter values from excel file. So In your excel file add the KPI & its filter values.

Load the excel file filter values & create the variables in the script by Sunny proposed method. The variable creation depend on the how you entered into excel file. Sunny proposed method works if you enter each value in to one row. I would recommend use clear KPI name for each value & that is useful if you add more KPI input values.

The other method, instead of excel file, you can use Input Box & users can directly enter any filed or values. You can control how many fields & values to enter or select to users.