Qlik Community

Qlik Sense App Development

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

madnanansari
New Contributor III

Sales Last Year Same Month Day

I am working Sales table have 3 years data with the below attributes:

[Date]

[Sales Amount]

The requirement is to show a TABLE where for each [Date], Sales for Last year same Month day should also come.

For example: Against the date 10-Dec-2017, the last year same Month day sales also come which is sales for 11-Dec-2016. (Sunday this month same as sunday last year same Month which is 11th december).

If I subtract 364 from [Date]; it will give me Last Year Same day.

18 Replies
antonaks
Contributor II

Re: Sales Last Year Same Month Day

Hi!

Try to use makedate()


makedate(current_year-1, month_of_current_year, day_of_current_year) as ....

madnanansari
New Contributor III

Re: Sales Last Year Same Month Day

what to do next then?

madnanansari
New Contributor III

Re: Sales Last Year Same Month Day

How to use set analysis to bring the sales for last year same month day?

antonaks
Contributor II

Re: Sales Last Year Same Month Day

Can you show how must final result seen? (Table or Diagram) May be I don't understand your question.

Re: Sales Last Year Same Month Day

May be use this approach

The As-Of Table

madnanansari
New Contributor III

Re: Sales Last Year Same Month Day

Date                     Sales      Sales LYSD

02-Dec-2016       101        0

03-Dec-2016       103        0

…..

…..

01-DEC-2017      55           101

02-Dec-2017       100          103

galax_allu
Valued Contributor

Re: Sales Last Year Same Month Day

Note :


you have to get  "Year" and "WEEK",   out of your "Date" field





Current/selected year

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



Prior year (to same week number as current/selected year):

  Sum({<WEEK = {"<=$(=Max({<Year={$(=MAX(Year))}>} WEEK))"}, Year = {$(=MAX(Year)-1)}>} Sales)

antonaks
Contributor II

Re: Sales Last Year Same Month Day

temp_data:

LOAD Date,

     Year(Date) as Year,

     Month(Date) as Month,

     Day(Date) as Day,

     num(Month(Date)) & '-' & Day(Date) as Month_Day,

     Sales

FROM

(ooxml, embedded labels, table is Лист1);

antonaks
Contributor II

Re: Sales Last Year Same Month Day

Try this.

Community Browser