topic Re: How to extract last month's value for every Quarter in App Development

How to extract last month's value for every Quarter

eddywong71 — Wed, 16 Aug 2023 07:31:25 GMT

Hi All,

Same as below link.

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

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)

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?

Re: How to extract last month's value for every Quarter

Lakshmanan — Wed, 16 Aug 2023 07:36:42 GMT

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)

Re: How to extract last month's value for every Quarter

eddywong71 — Wed, 16 Aug 2023 09:54:51 GMT

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

Re: How to extract last month's value for every Quarter

Lakshmanan — Wed, 16 Aug 2023 12:14:54 GMT

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.

Re: How to extract last month's value for every Quarter

eddywong71 — Thu, 17 Aug 2023 02:03:28 GMT

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 ?

Re: How to extract last month's value for every Quarter

vinieme12 — Thu, 17 Aug 2023 03:05:22 GMT

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)

Re: How to extract last month's value for every Quarter

PrashantSangle — Thu, 17 Aug 2023 04:13:26 GMT

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