topic Re: Help to get expression in QlikView

Help to get expression

Wed, 25 Nov 2020 16:16:04 GMT

Hi Team,

I have requirement where I am able to write query but not able to get in expressions. Can you please help me to get the expressions as per the requirement.

select sum(Target) from fact_table

where year = 2016

and

customer in (select customer from fact_table where Flag = 1 and year = 2017)

and

location in (select location from fact_table where Flag = 1 and year = 2017)

group by customer, location

Attached is the sample qvw I managed to create sorry for vague data set.

Re: Help to get expression

tyagishaila — Mon, 28 Nov 2016 08:05:41 GMT

Hi Krishana,

In your given sample data, Customer & Location for flag=1 and year=2017, there is no Target Value.

You can use calculated dimension in your chart

Dimensions:

location- If(Year=2017 and Flag=1, location)

customer- If(Year=2017, Flag=1, customer)

check - Suppress when value is Null

Expression:

Sum({<Year ={'2016'}>}Target)

Re: Help to get expression

Mon, 28 Nov 2016 08:24:57 GMT

Hi Shaila,

I want target for 2016 for the customer and location with flag equal to 1 in 2017.

I cannot use calculated dimension as I am using variable with input drop down box for dimension i.e user can change dimension from drop down.

Re: Help to get expression

tyagishaila — Mon, 28 Nov 2016 09:28:58 GMT

Is year field same for both customer/location & target?

Re: Help to get expression

MK_QSL — Mon, 28 Nov 2016 09:37:10 GMT

May be this..

=SUM(

Year = {'$(=Year(Today()))'},

customer = p({1<Flag = {1},Year = {'$(=Year(Today())+1)'}>}customer),

location = p({1<Flag = {1},Year = {'$(=Year(Today())+1)'}>}location)

>}Sales)

Re: Help to get expression

Thu, 08 Dec 2016 08:28:23 GMT

Hi Manish,

This is also not working. I have achieved with below expressions for Target in table.

sum({<Year = {2016}>}Target) * IF(Column(1)=0,0,1)

But I am not able to get same total in text box.

Let me know if there is any solution for it?

Regards,

Sai Krishna.

Re: Help to get expression

MK_QSL — Thu, 08 Dec 2016 15:23:13 GMT

Use this..

=SUM(

Year = {'$(=Year(Today()))'},

location = p({1<Flag = {1}, Year = {'$(=Year(Today())+1)'}>}location),

customer = p({1<Flag = {1}, Year = {'$(=Year(Today())+1)'}>}customer)

Target)