topic Re: Adding new data: Issues with expression and variable definition in QlikView

Adding new data: Issues with expression and variable definition

Fri, 20 Mar 2015 09:32:12 GMT

I am new to Qlikview and encountered some problems when I tried to add new data into the dashboard

My dashboard currently have only product data for 'A' and 'M'.

Currently

My variable for the product is written as an expression:

vProduct:

=if(product='A', 'A', if(Product='M', 'M', if(GetSelectedCount(Product)=0, 0, if(GetSelectedCount(Product)>=2, 2))))))

and a straight table is created with this expression:

=if(Product=vProduct, date(Date), if(vProduct=0 or vProduct=2, date(Date), null()))

Everything works OK until I tried adding 2 new product into the dashboard: 'E' and 'X'

So I tried modifying the vProduct definition to the following:

=if(Product='A', 'A', if(Product='M', 'M', if(Product='E', 'E', if(Product='X', 'X', if(GetSelectedCount(Model)=0, 0, if(GetSelectedCount(Model)=2, 2, if(GetSelectedCount(Model)=3, 3, if(GetSelectedCount(Model)=4, 4)))))))

and modifying the straight table expression to the following:

=if(Product=vProduct, date(Date), if(vProduct=0 or vProduct=2 or vProduct=3 or vProduct=2, date(Date), null()))))

I encountered a problem: When I select more than 2 products or 2 products, it returns results for all 4 product in the straight table.

Can someone help me please? Are there any errors in the above expression that I have modified?

Re: Adding new data: Issues with expression and variable definition

morganaaron — Mon, 23 Mar 2015 09:39:46 GMT

What are you trying to show in your table first and foremost? When somebody selects an individual product, or a combination of any but all 4, you want it to show the date, otherwise don't show anything? And is that the expression or do you mean dimension?

Re: Adding new data: Issues with expression and variable definition

Mon, 23 Mar 2015 09:54:33 GMT

Yes, I am trying to show date of the selected product or selected products- whether the user has selected one product, 2 product, 3 product or all 4 product (i.e. "A", "M", "E" or "X"), show the selected products or product 's date.

vProduct expression: =if(product='A', 'A', if(Product='M', 'M', if(GetSelectedCount(Product)=0, 0, if(GetSelectedCount(Product)>=2, 2))))))

is the definition of Variable: vProduct --> I found this in settings- variable overview

For the expression: =if(Product=vProduct, date(Date), if(vProduct=0 or vProduct=2, date(Date), null()))

This is written as an dimension

Re: Adding new data: Issues with expression and variable definition

morganaaron — Mon, 23 Mar 2015 14:50:29 GMT

I think I'm beginning to understand. Say your expression is to show sales, you only want to show sales that relate to the dates that the selected product(s) were sold on? If so, when selecting a product in a list box, it will naturally do this - limiting the data to your selections. If you want to show all sales (not just for the product but keeping to the dates that are attached) then there a few options.

If it's just dealing with nulls that are your issue, there are various ways to combat it. Essentially I'm asking, why can't you just create a list box with the products in that would filter your table? Is there only one date attached to each product? Or are you simply trying to show a table with product and date?

Re: Adding new data: Issues with expression and variable definition

jonathandienst — Mon, 23 Mar 2015 15:01:27 GMT

This expression contains 'naked fields' (ie without some sort of aggregation. See this article: http://community.qlik.com/blogs/qlikviewdesignblog/2014/06/16/use-the-aggregation-functions#comment-18036

HTH

Jonathan

Re: Adding new data: Issues with expression and variable definition

ashwanin — Mon, 23 Mar 2015 15:06:44 GMT

Hi Florence,

Can you try below.

vProduct:

=if(product='A', 'A', if(Product='M', 'M',if(product='E', 'E',if(product='X', 'X', if(GetSelectedCount(Product)=0, 0, if(GetSelectedCount(Product)>=4, 4))))))

and a straight table is created with this expression:

=if(Product=vProduct, date(Date), if(vProduct=0 or vProduct=4, date(Date), null()))

Re: Adding new data: Issues with expression and variable definition

Tue, 24 Mar 2015 01:21:56 GMT

Hi ASHWANIN,

Still Doesnt work

After entering the suggested expression, when i select the product individually, it shows data (same as the before) and when i select 2 or more than 2 product, the straight table shows no data

Re: Adding new data: Issues with expression and variable definition

Tue, 24 Mar 2015 01:35:42 GMT

I have tried this method, but I dont seems to be able to work.

Same thing happens. When I select 1 product. It filters well. When I select more than 1 product, it shows data for all products

Re: Adding new data: Issues with expression and variable definition

morganaaron — Tue, 24 Mar 2015 08:50:48 GMT

Hi Florence,

Can you upload your qvw? As Jonathan said, if you have multiple dates to show then you need some sort of aggregation function to pick one - otherwise it's going to return nothing as it doesn't know which date to pick. If you simply want to limit the range based on what you're selecting there are plenty of other less complicated ways to do so! If you have product as your dimension and Only(Date) as your expression (assuming there is only one date per product) this will show for whatever products or combination you then select. If there are multiple dates per product, you'll need to decide what you want to show - the max date, min date? If you want to show all dates that relate to any selected products, create a table box with product and date and it should show you all related dates.

Re: Adding new data: Issues with expression and variable definition

Tue, 24 Mar 2015 09:03:23 GMT

Hi Aaron,

currently the Date (date) range expression is written as:=if(Date >=vMinSelectedDateNews and Date <=vMaxSelectedDateNews, Activity_News)

Now, I need to write an expression to display the product or the product combination that is selected.

For example, if Product A is selected, Date (date) for Product A is displayed. If Product B and A is selected, the Date (date) for Product A and B is selected. If 3 products selected , for example, Product A, B, D etc, then Date (date ) for Product A, B and D will be displayed.

Re: Adding new data: Issues with expression and variable definition

morganaaron — Tue, 24 Mar 2015 10:59:25 GMT

Hi Florence,

What's contained in the Activity_News field? Is that Date(date) a variable or a dimension? I think it's all going to revolve around the aggregation issue as Jonathan said.