Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Highlighted
Not applicable

sum DaySales vs DaySalesLastYear (same weekday)

Hi,

i have to do a comparison between sales of a date vs sales of last year for the same date and same weekday.

For example, I have two expressions:

            Sales

            SalesLastYear

If I select 10 november (Thursday) then:

  1. In sales, I have to display sales of 10 nov 2016
  2. In SalesLastYear, I have to display sales of 12 nov 2015 (10 nov 2015 is Monday, so I have to compare the sales for the first Thursday most approximate, that’s it, 12 nov 2015)

How I can calculate SalesLastYear?

6 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: sum DaySales vs DaySalesLastYear (same weekday)

Perhaps like this:

Selected date: sum(Sales)

Last Year: sum({<MyDateField={"$(=Date(Max(MyDateField)-364,'DD MMM YYYY'))"}>}Sales)


talk is cheap, supply exceeds demand
Highlighted

Re: sum DaySales vs DaySalesLastYear (same weekday)

I think it might also make sense to look at The As-Of Table‌ since you are doing a weekday wise comparison here.

Highlighted

Re: sum DaySales vs DaySalesLastYear (same weekday)

Its funny how you proposed As-Of-Table in another post and I proposed another alternative and now you proposed another alternative and I ended up proposing the AsOfTable approach

Highlighted
New Contributor

Re: sum DaySales vs DaySalesLastYear (same weekday)

= if((Mod(Max(Year),4)=0 and Mod(Max(Year),100)<>0) or Mod(max(Year),400)=0,


Sum( {<Date={"$(=(Date((Max(Date)-365),'DD/MM/YYYY')))"},Year=,Month=>} [Sales] ),


Sum( {<Date={"$(=(Date((Max(Date)-364),'DD/MM/YYYY')))"},Year=,Month=>} [Sales]))

 

This expression will ensure that Leap Year is not missed 🙂

Highlighted
Valued Contributor II

Re: sum DaySales vs DaySalesLastYear (same weekday)

@sunny_talwar / @Gysbert_Wassenaar ,

Do you have a sample application of this approach , I would like to go through it for a better understanding 

Highlighted

Re: sum DaySales vs DaySalesLastYear (same weekday)

Did you look at the link shared above (The As-Of Table)? It does give sample code?