topic Re: Set Analysis for Yd's Data in QlikView

Set Analysis for Yd's Data

Fri, 17 May 2013 12:13:27 GMT

Hi,

I have a straight table with dimensions Company, Date, Yd Date

Expressions : Cost , Yd Cost

I am having trouble displaying Yd Cost.

Company, Date, and Cost come from the same table but Yd Date comes from a different table and is joined by Date field. I tried set analysis:

sum({<[DATE]= {'$(=Date(max({<[YD DATE] = {"<$(=max([DATE]))"}> } [YD DATE])))'}>} COST)

The idea was for it to pick the max YD DATE which is less than DATE but it doesn't work. I would like to see a list of dates but with using MAX it will only display one record.

Re: Set Analysis for Yd's Data

Fri, 17 May 2013 13:07:06 GMT

try this

=sum({<[YD DATE]= p({$<max([YD DATE]) = {"<$(=max([DATE]))"}> } [YD DATE])>} COST)

or this

=sum({<[YD DATE]= p({$<max([YD DATE]) = {"<max([DATE])"}> } [YD DATE])>} COST)

Re: Set Analysis for Yd's Data

Fri, 17 May 2013 13:29:03 GMT

Gives syntax error and when that's changed, just zeros

Re: Set Analysis for Yd's Data

CELAMBARASAN — Fri, 17 May 2013 13:59:50 GMT

Check with this

=sum({<[DATE]={"=[DATE]<[YD DATE]"}>} COST)

Re: Set Analysis for Yd's Data

Fri, 17 May 2013 14:05:26 GMT

Thanks.

If I change < to > like this:

=sum({<[DATE]={"=[DATE]>[YD DATE]"}>} COST)

Then costs are displayed but for [DATE], not [YD DATE]

Would it be possible to show costs for max YD DATE excluding DATE?

Re: Set Analysis for Yd's Data

CELAMBARASAN — Fri, 17 May 2013 14:23:56 GMT

not clear. can you explain clearly if possible with some examples.

Re: Set Analysis for Yd's Data

Fri, 17 May 2013 14:34:01 GMT

COMPANY DATE COSTS YD DATE YD COSTS

ABC 16/01/2013 2000 17/01/2013 1900

ABC 17/01/2013 1800 16/01/2013 2000

This is what it should look like. I can't get YD COSTS. If I use

=sum({<[DATE]={"=[DATE]>[YD DATE]]"}>} COST)

Then I get this:

COMPANY DATE COSTS YD DATE YD COSTS

ABC 16/01/2013 2000 17/01/2013 2000

ABC 17/01/2013 1800 16/01/2013 1800

YD COSTS are not right

Re: Set Analysis for Yd's Data

Fri, 17 May 2013 15:25:41 GMT

I need to get YD COSTS for YD DATE.