topic Re: Sum same period last year in App Development

Sum same period last year

papacito — Wed, 11 Sep 2024 21:50:13 GMT

I have this dataset

so for thisyear in the tabel i just put

Date | Pool

for the dimention in date I put:

=If(
Month(Data) = Month(Today()) and
Year(Data) = Year(Today()),
Date(Data, 'DD-MM-YYYY')
)

for the measure of pool:

Sum({<Descricao = {'UHS DISPONÍVEIS','TOTAL OCUPAÇÃO'} >} Valor1)

now i need another table with the same day-month-year of last year

so for

Date | Pool

for Date i put:

=If(
Month(Data) = Month(Today()) and
Year(Data) = Year(Today()),
AddYears(Data,-1)
)

it works just fine,

but i cant seem to get the last years sum working.

if i wanted to sum only the month I would do :

=Sum(
{<[Data.ac.Ano] = {">=$(=YearStart(Today(),-1))<=$(=Addmonths(Today(),-12))"}>}
Valor1
)

but how can I put it to work with the days as well.

I have tried many ways withouut success.

Re: Sum same period last year

PrashantSangle — Thu, 12 Sep 2024 04:21:51 GMT

try below

for Current month Current Year

Chart > Table

Dimension - Data

Measure - Sum({<Data={">=$(=MonthStart(Today()))<=$(=MonthEnd(Today()))"},Descricao = {'UHS DISPONÍVEIS','TOTAL OCUPAÇÃO'} >} Valor1)

****************************************************************************************************************

for Current month Last Year

Chart > Table

Dimension - Data

Measure - Sum({<Data={">=$(=MonthStart(Today(),-12))<=$(=MonthEnd(Today(),-12))"},Descricao = {'UHS DISPONÍVEIS','TOTAL OCUPAÇÃO'} >} Valor1)

Regards,

Prashant Sangle

Re: Sum same period last year

papacito — Thu, 12 Sep 2024 13:09:47 GMT

hi, thanks for you quick response, but it doesnt work in this scenario because i need it to work at day level.

Re: Sum same period last year

PrashantSangle — Thu, 12 Sep 2024 14:41:48 GMT

It will work. Might be need to do some changes.. like date format issue etc

Can you share app or sample data, so that we will work on it.

Re: Sum same period last year

papacito — Thu, 12 Sep 2024 19:11:37 GMT

I use Qlik sense bro, aint no file. if you could have a check on my computer like meets I would appreciate. (edited for privacy)

Re: Sum same period last year

Kushal_Chawda — Fri, 13 Sep 2024 09:32:19 GMT

@papacito You need to create a calendar with all the Dates. I am assuming you already have calendar. below are the steps after creating calendar

Calendar_Final:
Load Distinct Date,
Date as Period,
'CY' as Flag
Resident Calendar;

Load Distinct Date as Period,
addyears(Date,-1) as Date,
'PY' as Flag
Resident Calendar;

You can Now create chart with below

Dimension: Period

Expression:

Current year:

Sum({<Descricao = {'UHS DISPONÍVEIS','TOTAL OCUPAÇÃO'} , Flag={'CY'}>} Valor1)

Same period last year:

Sum({<Descricao = {'UHS DISPONÍVEIS','TOTAL OCUPAÇÃO'} , Flag={'PY'}>} Valor1)