Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.