Set Analysis related query

[#1]

Hi,

i have a Column (Field Name) MemberID , i am showing records as MemberId wise SUM of Orders.

Like Below :

Now i want to add a another Column which have difference of each member with Member Id 2 . that means each record will subtarct by 14069 ( Memberid 2 No. of Orders) . i have observe that when we use the same Field which is already a part of the Column the set analysis did not work or work only for the record which is matching below is result information

The is just a example the same i have to do with other columns also. i want to use same on memberid, datewise.

[MK_QSL]

Can you provide sample data related to your query... Little confusion about MemberID 2 !

[#3]

Dear Manish the above was just for understanding purpose what output i actually want is in for of excel file.

[MK_QSL]

Use below script...

TEST:

LOAD MemberId,

     Date,

     [sum of Orders] as Orders

FROM

ResultQlikview.xlsx

(ooxml, embedded labels, table is Sheet1);

Left Join

Load MemberId, Date(Min(Date)) as MinDate Resident TEST Group By MemberId;

Create a Straight Table

Dimension

MemberId

Date

Expressions

1) SUM(Orders)

2) SUM(Orders) - Min(TOTAL <MemberId> Aggr(SUM({<Date = {"= Date = MinDate "}>}Orders),MemberId))

[#5]

I dont want to use script for this i want to so with set analysis only, because my minimum i want to be filxible, that means if i select data from Mar-2014 to June-2014 the 1st Mar 2014 will br my MIN date, if i select from Jun-2014 to Sep-2014 then my 1st June 2014 will be the MIN Date.

[#6]

I can not understand the limitation that when a particular Field ( Column) became a part of the table, then SET ANALYSIS not work as per its standard.

Why it is so that we can only use Set Analysis on the field ( Column ) which is not part of the table.

Strange ..

[MK_QSL]

Without Changing the Script

SUM(Orders) - Min(TOTAL <MemberId> Aggr(SUM({<Date = {"=Aggr(Date(MIN(TOTAL <MemberId> Date)),MemberId,Date)"}>}Orders),MemberId))

[#8]

I am looking into this once i verify i will let you know, first cut it is working  fine.