Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
New Contributor III

Sum for the date and for the same date in the previous year in the rows of a table

Hi Community,

I am trying unsuccessfully to show in the same row the value corresponding to the same date but from the previous year.

I tried different expressions of set analysis from the community but without success:

= SUM({< [SalesDate] = {$(=AddYears([SalesDate],-1))} >} [Price_ExtRetail])

= SUM({< [SalesDate] = {"$(=AddYears([SalesDate],-1))"} >} [Price_ExtRetail])

2019-10-18_153905.png

If someone can give me some light on this matter I really appreciate it.

 

Thanks!

Labels (2)
6 Replies
Highlighted
Valued Contributor III

Re: Sum for the date and for the same date in the previous year in the rows of a table

H

Include this in your (calendar) script

AddMonths( SalesDate,-12) as PrevYrSalesDate , 

Then make your selections and do this in set analysis

SalesDate = P(PrevYrSalesDate) , MonthYear = , Year = , Month = etc

Take out all date selections made in the measure ie MonthYear etc

NB Leap yr 29/2 matched to 28/2

Highlighted
Luminary
Luminary

Re: Sum for the date and for the same date in the previous year in the rows of a table

Try this:

= SUM({< [SalesDate] = {"$(=AddYears([SalesDate],-1))"} >} [Price_ExtRetail])
Blog: WhereClause   Twitter: @treysmithdev
Highlighted
New Contributor III

Re: Sum for the date and for the same date in the previous year in the rows of a table

Thank @treysmithdev for you reply, in my post I forgot the comma before -1 in the AddYears formula, but I had already dealt with the expression you indicate but without success.

Highlighted
New Contributor III

Re: Sum for the date and for the same date in the previous year in the rows of a table

@robert99, Can you tell me if I have any errors in my set analysis?

I tried what you said but I receive null for all rows..

=SUM({<SalesYear = , SalesMonth =, SalesDay = , SalesDate = {P(SalesDatePY)} >} Price_ExtRetail)

 

With these I receive 0 for all rows:

=SUM({< SalesDate = {"$(=P(SalesDatePY))"} >} Price_ExtRetail)

=SUM({< SalesYear = , SalesMonth =, SalesDay = , SalesDate = {"$(=P(SalesDatePY))"} >} Price_ExtRetail)

 

Thanks!

Highlighted
Valued Contributor III

Re: Sum for the date and for the same date in the previous year in the rows of a table

Hi

Try

SalesDate = P(SalesDatePY)

not

SalesDate = {P(SalesDatePY)}

The outer brackets should be removed

NB If this doesn't work check using a table and dimensions of SalesDate and SalesDatePY to ensure each SalesDate has the correct previous years date

 

Highlighted
New Contributor III

Re: Sum for the date and for the same date in the previous year in the rows of a table

@robert99, same result 😞
I decided to use the calendar with flags to solve it, but logically I had to add the flag to all my calculations so that the values are not disturbed.

Thanks both of you for your collaborations. @robert99  and @treysmithdev .