How to extract last month's value for every Quarter

[eddywong71]

Hi All,

Same as below link.

https://community.qlik.com/t5/App-Development/How-to-extract-last-month-s-value-for-every-Quarter/td...

I want to create a line Chart only Show Q1,Q2,Q3,Q4 just like below 

By using 

Dimension:

Dual('Q'&Num(Ceil(Num(Month(InvDate))/3)),Num(Ceil(NUM(Month(InvDate))/3),00))

Measure:

Sum({<InvId = {'=Month(InvDate)=Month(QuarterEnd(InvDate))'}>}Sales)

OR

Sum(If(Month(InvDate)=Month(QuarterEnd(InvDate)),Sales,0)).

However The Line Chart sum with all the year. I want the Line chart have two line

Current Year , Last Year. like below picture

how should i revise the measure.

Can anyone help?

[Lakshmanan]

Hi ,

Dimension:

Dual('Q'&Num(Ceil(Num(Month(InvDate))/3)),Num(Ceil(NUM(Month(InvDate))/3),00))

Measure :

Create two measures 

By Formula use

=Sum({<Year = {"$(=Max(Year))"}>} Sales)

Previous Year Sales

=Sum({<Year = {'$(=Max(Year)-1)'}>} Sales)

[eddywong71]

Hi Lakshmanan,

By using expression you provided, still sum up all the year like below. Current Year Last Year like below.

In my data , Month represent Date like 202312

I would like Current Year Q1 = Mar2023  Q2 = Jun2023, Q3 =Sep2023, Q4 = Dec203

[Lakshmanan]

Considering as 202312

Dimensions :

='Q'&Ceil(right(InvDate,2)/3) 

gives as Q1,Q2,Q3,Q4

if you want with Year 

='Q'&Ceil(right(InvDate,2)/3) &'-'&Left(InvDate,4)

Measures :
Max Year -1  :

=Sum({<InvDate = {'>=$(=Date(Yearstart(Date#(Year(max(DATE#(InvDate,'YYYYMM')))-1,'YYYY')),'YYYYMM'))<=$(=Date(YearEnd(Date#(Year(max(DATE#(InvDate,'YYYYMM')))-1,'YYYY')),'YYYYMM'))'}>} Sales)

Max Year :

=Sum({<InvDate = {'>=$(=Date(Yearstart(max(Date(DATE#(InvDate,'YYYYMM')))),'YYYYMM'))<=$(=Date(YearEnd(max(Date(DATE#(InvDate,'YYYYMM')))),'YYYYMM'))'}>} Sales)

Modify above according with your fields .

Please mark as closed ,if it resolved the issue.

[eddywong71]

Hi Lakshmanan,

I revise like this because i already load data set   ---  Date#([Month],'YYYYMM') as [Month],:

Dimensions :

='Q'&Ceil(right(Month,2)/3) 

Max Year :

=Sum({<InvDate = {'>=$(=Date(Yearstart(max(Date(DATE#(InvDate,'YYYYMM')))),'YYYYMM'))<=$(=Date(YearEnd(max(Date(DATE#(InvDate,'YYYYMM')))),'YYYYMM'))'}>} Sales)

Month = {"$(='>=' & Date(Yearstart(Max(Month))) & '<' & Date(YearEnd(Max(Month))) )"}>}

This Time Q1= Jan 2023 + Feb2023 +Mar2023.

How can Q1 = Current Year = Mar2023 ,Last Year = Mar 2022 instead ?

[vinieme12 · Accepted solution]

Simplify your expressions by creating a flag field in the data load script to flag quarter end dates

Assuming you have a master calendar, create a flag field as below

Mastercalendar:

Date as InvDate

,Year(Date) as YEar

,MonthNAme(Date) as MonthNAme

,if(Date=QUARTEREND(Date),1,0) as flag_QuarterEnd

.......

sum({<flag_QuarterEnd={1}>}SomeValue)

[PrashantSangle]

Create quarter field in backend (script), will resolve your pain.

once you create quarter field in script then use same measure which suggested by Lakshaman