topic Re: SET ANALYSIS in QlikView

SET ANALYSIS

Mon, 10 Nov 2014 10:16:07 GMT

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.

Re: SET ANALYSIS

Mon, 10 Nov 2014 10:18:36 GMT

Difference should be always from the Minimum date sum of orders

Re: SET ANALYSIS

Mon, 10 Nov 2014 10:22:19 GMT

try like this

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

Re: SET ANALYSIS

Mon, 10 Nov 2014 10:24:59 GMT

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

Re: SET ANALYSIS

Mon, 10 Nov 2014 10:33:17 GMT

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)

Re: SET ANALYSIS

Mon, 10 Nov 2014 10:33:45 GMT

SUM( {$} ORDERS)

Re: SET ANALYSIS

Mon, 10 Nov 2014 10:35:06 GMT

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

Re: SET ANALYSIS

MayilVahanan — Mon, 10 Nov 2014 10:42:51 GMT

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.

Re: Re: SET ANALYSIS

Mon, 10 Nov 2014 10:44:21 GMT

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

Re: Re: SET ANALYSIS

Mon, 10 Nov 2014 10:50:14 GMT

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 ..

Re: SET ANALYSIS

aveeeeeee7en — Mon, 10 Nov 2014 10:56:08 GMT

Try this Expression:

SUM(Orders)-SUM({<Date={'$(=MIN(Date))'}>}Orders)

Regards

Av7eN

Re: Re: SET ANALYSIS

Mon, 10 Nov 2014 10:56:55 GMT

try this ,

sum(ordres)-firstsortedvalue(orders,date)

Re: SET ANALYSIS

Mon, 10 Nov 2014 11:01:09 GMT

order is not datewise single record. i required to use sum()

Re: SET ANALYSIS

Mon, 10 Nov 2014 11:03:20 GMT

Dear Sir,

i already told before the formula u have given work perfectly fine, when the date is not a part of the table Column,

when i add the date as a column into the table the above formula get fail.

please find screen shot for same.