SET ANALYSIS

[#1]

Dear Team, I have a table like Name , Date, sum(orders) . i want to create on more expression in the Table which sum(orders) - MIN date sum of Orders example given below:

Client	Date	Orders	Result
A	20-Apr-14	100	0
A	21-Apr-14	150	50
A	22-Apr-14	200	100
B	1-Jan-14	50	0
B	2-Jan-14	100	50
B	3-Jan-14	200	150

In the above table result column is the difference ( the difference in future may be in % or other formula also)  of the RED, Clientwise.

[#2]

Difference should be always from the Minimum date sum of orders

[#3]

try like this

sum(orders)-aggr(sum(orders),min(date))

[#4]

sum({<date={$(=min(date)}>}orders)-sum(orders)

[#5]

I HAVE TRIES ALL NOT WORKING BECAUSE DATE ITSELF IS THE PART OF THE DIMENTION, IF DATE IS NOT PART OF DIMENTION THEN ITS WORKING. SUM( {$} ORDERS)

[#6]

SUM( {$} ORDERS)

[#7]

no i could't understand what ur saying pls tell me clearly once

[MayilVahanan]

Hi

Try like this

ClientTemp:
LOAD Client,
     Date(Date#(Date,'D-MMM-YY'),'D-MMM-YY') as Date,
     Orders
FROM
[http://community.qlik.com/thread/141438]
(html, codepage is 1252, embedded labels, table is @1);

Left Join(ClientTemp)
Load Client, FirstSortedValue(Orders, Date) as MinOrders Resident ClientTemp Group By Client;

LOAD *, Orders - MinOrders as Result Resident ClientTemp;

DROP Table ClientTemp;

Here, Result column gives your output.

[#9]

WHEN I REMOVE THE ORDER_DATE COLUMN FROM THE THE TABLE THEN BELOW MENTION EXPRESSION IS ABLE TO GET THE MINIMUM DATE ORDERS, BUT WHEN I AM INCLUDING THE DATE INTO THE TABLE THEN ONLY MATCHING DATE DISPLAY THE VALUE OTHER DATE SHOW ZERO .. PLEASE FIND ATTACHED SCREEN SHOT FOR OUTPUT

SUM(  { $ <ORDER_DATE= {'$(vMinOrderDate) ' }> } ORDERS)

WHEN DATE IS THERE THEN EXPRESSION SHOW ZERO

WHEN DATE IS REMOVED ITS WORK FINE

[#10]

Hi,

I dont want to do with sperate load and temp table etc, is there is any way to do by Set Analysis only ??

it is why when the particular fields is part of Column in the table the set Analysis not work on it ..