issues with neglecting dimension from set analysis in qlik sense

[vikasmahajan]

Hi All,

I am  trying to find out  new customers added after cutt-off date.  I have customer master with there  Created_on. Apart from this I have more calendar for link transactions with dimensions.

I have created calendar using following script :

TEMP:

LOAD Max(Created_on) AS MaxDate

FROM [lib://3_Qvds/ROMaster.qvd](qvd);

LET varMaxDate = Num(peek('MaxDate', 0, 'TEMP'));

TEMP1:

LOAD Min(Created_on) AS MinDate

FROM [lib://3_Qvds/ROMaster.qvd](qvd);

LET varMinDate = Num(peek('MinDate', 0, 'TEMP1'));

//*************** Temporary Calendar ***************

TempASOF:

LOAD

$(varMinDate)+IterNo()-1 AS AsOfDate,

Date($(varMinDate)+IterNo()-1) AS N_Created_on

AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

drop table TEMP;

drop table TEMP1;

ASOF:

LOAD

  AsOfDate,

  N_Created_on as Created_on,

  Month(N_Created_on) AS N_MonthName,

  Date(MonthStart(N_Created_on),'MMM-YY') AS N_MonthYear,

  Date(N_Created_on,'MMM') &'-'& Date(N_Created_on,'YY') as  N_YearMonth,

  If(Num(Month(N_Created_on))>3,Num(Month(N_Created_on))-3,Num(Month(N_Created_on))+9) AS N_MonthNumber,

  'Q' & alt(if(Month(N_Created_on)<4,4),if(Month(N_Created_on)<7,1),if(Month(N_Created_on)<10,2),3) AS N_Quarter,

  if(Num(Month(N_Created_on))>3,Year(N_Created_on)+1,Year(N_Created_on)) AS N_FiscalYear,

  YearName(N_Created_on,0,4) AS N_FinancialYear

Resident  TempASOF;

Drop table TempASOF;

//Exit Script;

and following expression

Chart

Dimension//;

N_YearMonth,N_Quarter,N_FinancialYear

Measures://

num(RangeSum(Above(Count( {<COMPANY={'ABCD'}, Created_on_Flr={'>=$(=vCutOffDate3)'}>} Distinct CCNO),0,RowNo())),'##,##,###')

Using this expression i can able to find out new customers added.

My Issue is when i select N_YearMonth  this i want to neglect from following expression :

Sum( <N_YearMonth=> {<COMPANY={'ABCD'}, FiscalYear>} VolKL)

using  N_YearMonth= i neglect the dimension but my set expression is not ignoring this dimension.

any body know how to overcome this issue?

thanks in adv.

Vikas

[petter]

If your expression is exactly like this:

          Sum( <N_YearMonth=> {<COMPANY={'ABCD'}, FiscalYear>} VolKL)

It is syntactically not correct - it has to be like this to work at all:

    Sum( {<COMPANY={'ABCD'}, FiscalYear=,N_YearMonth=>} VolKL)

Then it is syntactically correct - but that doesn't guarantee that it will work like you want it to. For example it might be that you will have to neglect other dimensions that the end-user might have done selections with that are related to the two you already have. Then they have to be ignored as well.

[shiveshsingh]

Can you try this

Sum( {<N_YearMonth,COMPANY={'ABCD'}, FiscalYear>} VolKL)