Help to get expression

[#1]

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.

[tyagishaila]

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)

[#3]

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.

[tyagishaila]

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

[MK_QSL]

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)

[#6]

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.

[MK_QSL]

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)