topic Re: Aggregate function year to month in QlikView

Aggregate function year to month

karan_kn — Wed, 25 Nov 2020 16:16:04 GMT

Can you help me to update the Aggrgate function with Year to Month in Load statement:

1-12 months - AAA

13-36 months - BBB

37-60 months - CCC

if(aggr(max(year(Sales_Date)),CustID)-aggr(max(year(Sales_Date),2),CustID)=1,'AAA',

if((aggr(max(year(Sales_Date)),CustID)-aggr(max(year(Sales_Date),2),CustID))>=2 and ((aggr(max(year(Sales_Date)),CustID)-aggr(max(year(Sales_Date),2),CustID))<=3),'BBB',

if((aggr(max(year(Sales_Date)),CustID)-aggr(max(year(Sales_Date),2),CustID))>=4 and ((aggr(max(year(Sales_Date)),CustID)-aggr(max(year(Sales_Date),2),CustID))<=5),'CCC')))

Re: Aggregate function year to month

sunny_talwar — Tue, 16 Jan 2018 14:59:54 GMT

May be like this

Fact:

LOAD CustID,

Sales_Date,

Year(Sales_Date) as Year,

...

FROM ...;

Left Join (Fact)

LOAD Cust_ID,

If(MaxYear - MaxYear2 = 1, 'AAA',

If(MaxYear - MaxYear2 <= 3, 'BBB',

If(MaxYear - MaxYear2 <= 5, 'CCC'))) as Flag;

LOAD Cust_ID,

Max(Year) as MaxYear,

Max(Year, 2) as MaxYear2

Resident Fact

Group By Cust_ID;

Re: Aggregate function year to month

karan_kn — Tue, 16 Jan 2018 15:10:32 GMT

Thanks Sunny for your response, but I'm looking for months

1-12 months - AAA

13-36 months - BBB

37-60 months - CCC

Re: Aggregate function year to month

sunny_talwar — Tue, 16 Jan 2018 15:12:46 GMT

I am not sure what you mean... do you have a sample or sample data you can share? and explain what you need as an output?