Qlik Community

Qlik Sense App Development

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

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
madnanansari
Contributor

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
Contributor

Re: Sales Last Year Same Month Day

what to do next then?

madnanansari
Contributor

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
Contributor

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.