topic Re: Set Analysis related query in QlikView

Set Analysis related query

Mon, 10 Nov 2014 11:38:19 GMT

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.

Re: Set Analysis related query

MK_QSL — Mon, 10 Nov 2014 11:39:22 GMT

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

Re: Re: Set Analysis related query

Mon, 10 Nov 2014 11:53:48 GMT

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

Re: Set Analysis related query

MK_QSL — Mon, 10 Nov 2014 12:34:57 GMT

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

Re: Set Analysis related query

Mon, 10 Nov 2014 12:48:32 GMT

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.

Re: Set Analysis related query

Mon, 10 Nov 2014 12:58:23 GMT

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

Re: Set Analysis related query

MK_QSL — Mon, 10 Nov 2014 13:09:11 GMT

Without Changing the Script

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

Re: Set Analysis related query

Mon, 10 Nov 2014 13:29:21 GMT

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