Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
kwarren1
Partner
Partner

Rolling Calendar - 1,0 Identifier Flags Not Working as Intended

Help:

I cannot seem to flag Last12months field column successfully in my script to output 1's in the appropriate rows, which is making the following expression unsuccessful.

sum(aggr(sum({$<Last12MonthsFlag={1},Category = {'Income', 'Interest Income', 'Paycheck','Federal Tax','State Tax'}>} Amount), MonthYear))


Below is the script Detail that is also in the app.


LOAD

    "Date",

    Amount,

    "Transaction Type",

    Category,

    "Account Name"

FROM [lib://Data/mint_transactional_data_masked.xlsx]

(ooxml, embedded labels, table is transactions);

MinMaxTemp:

LOAD

    MIN(Date) as MinDate,

    MAX(Date) as MaxDate

RESIDENT transactions

;

    LET vMinDate = NUM(PEEK('MinDate',0,'MinMaxTemp'));

    LET vMaxDate = NUM(PEEK('MaxDate',0,'MinMaxTemp'));

    LET vToday = $(vMaxDate);

;

CalTemp:

LOAD

    DATE($(vMinDate) + ROWNO() - 1) as TempDate

  AUTOGENERATE

          $(vMaxDate) - $(vMinDate) + 1;

        

DROP TABLE MinMaxTemp

;

MasterCalendar:

Load

    TempDate as Date,

    YEAR(TempDate) as Year,

    Month(TempDate) as Month,

    Month(TempDate)&'-'&Year(TempDate) AS MonthYear,

  // Month() as DisplayMonth,

    Week(TempDate) as Week,

    Day(TempDate) as Day,

    'Q' & Ceil(Month(TempDate)/3) as Quarter,

  

    InYearToDate(TempDate,$(vToday),0) * -1 as CYTDFlag,

    InYearToDate(TempDate,$(vToday),-1) * -1 as LYTDFlag,  

  

    If(DayNumberOfYear(TempDate) <= DayNumberOfYear($(vToday)),1,0) as IsInYTD,

    If(DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter($(vToday)),1,0,) as IsInQTD,

    If(Month(TempDate) = Month($(vToday)),1,0) as IsCurrentMonth,

    If(Month(AddMonths(TempDate,1)) = Month($(vToday)),1,0) as IsLastMonth

  

RESIDENT CalTemp

ORDER BY TempDate ASC

;

Drop Table CalTemp;

// 1. Create a distinct list of Months:

TransactionMonths:

Load distinct

Month

Resident MasterCalendar

ORDER BY Month ASC;

;

// 2. Create a distinct list of DisplayMonths:

DisplayMonths:

Load

Month as DisplayMonth

Resident

TransactionMonths

;

// 3. Create a list of all possible combinations:

join (TransactionMonths) load * resident DisplayMonths

;

// 4. Reload the same table and calculate all the necessary flags:

MonthsLink:

Load

Month,

IF( Month >= AddMonths(DisplayMonth, -6) , 1, 0) as Last6MonthsFlag,

IF( Month >= AddMonths(DisplayMonth, -12) , 1, 0) as Last12MonthsFlag,

IF( Month = DisplayMonth , 1, 0) as SameMonthFlag

Resident

TransactionMonths

;

drop table TransactionMonths;

drop table DisplayMonths;

2 Replies
jayaseelan
Creator III
Creator III

Hi Kevin,

     Try the following query with you master calendar. hope this will gives your output.

     If((TempDate) > addmonths($(varMaxDate),-12) and (TempDate) <= Today(),1) as [Rolling Month 12],

kwarren1
Partner
Partner
Author

Worked great! Thank you so much!