2 Replies Latest reply: Jun 12, 2018 6:55 AM by Shivesh Singh RSS

    issues with neglecting dimension from set analysis  in qlik sense

    vikas mahajan

      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