Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
Partner
Partner

Show Today's Sales and Same Day Last Year's Sales

Hello,

I am creating a straight table to show the current day's sales amount and the same date last year's sales amount with variance by store (see attached screen shot of table layout and data model).   I have found postings on comparing sales amounts between the current date and the same day last year, but the suggested solutions are not applicable to what I am trying to solve.  I am having difficulty putting the current date and the current date sales amount with the same day last year's sales amount on the same row by Store. 

I need to just show the current day's sales (i.e. Max(date) function) and the same date last year's sales .  For example, sales for all stores for today 1/9/2016 and 1/9/2015 with variance.  Each day the dashboard needs to only show that day's sales with prior date sales. 

I believe the right approach is to use set analysis expressions, but I'm not sure of the specific syntax to get the table to show the data. 

Any help on the coding is always highly appreciated! 

Thank you,

1 Solution

Accepted Solutions

Re: Show Today's Sales and Same Day Last Year's Sales

Hi Alec,

I think the problem with the date format..the below expression working fine..

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

Capture.JPG

Check the Attachment..

View solution in original post

17 Replies

Re: Show Today's Sales and Same Day Last Year's Sales

I think if I understand your requirement correctly, you will need to use Above() function to achieve what you are looking to do.

Re: Show Today's Sales and Same Day Last Year's Sales

Hi,

Normally we do like this

Current year max date sales

=Sum({$< Date={"$(=Date(Max(Date)))"}>}Sales)

Previous :

=Sum({$< Date={"$(=Date(AddYears(Max(Date),-1)))"}>}Sales)

Not sure is this your need..

Partner
Partner

Re: Show Today's Sales and Same Day Last Year's Sales

Hi,

I tried the expressions, but no data is appearing. I did change the Sales field to the sales field in the data model.  The updated expressions are now: 


  • =Sum({$< Date={"$(=Date(Max(Date)))"}>} [Net Sales]
  • =Sum({$< Date={"$(=Date(AddYears(Max(Date),-1)))"}>} [Net Sales])

Not sure why no data is appearing. 


Re: Show Today's Sales and Same Day Last Year's Sales

I think since you have the date field in your dimension, the simple set analysis won't work for you. You will need to use Above() function to make it work. For the exact expression, you will need to provide a sample data.

Partner
Partner

Re: Show Today's Sales and Same Day Last Year's Sales

Hi Sunny,

Please see attached sample qvw and data source.

Thank you,

Re: Show Today's Sales and Same Day Last Year's Sales

Try this:

=If(Sum({$< Date={"$(=Date(Max(Date)))"}>} [Net Sales]) > 0, Above(Sum({<Date>}[Net Sales])))

Sorry I just realized this previous day and you are looking for previous year. But the idea is still to use Above() function to achieve that

Partner
Partner

Re: Show Today's Sales and Same Day Last Year's Sales

Hi Sunny,

I added your expressions and I'm still not getting any data. I attached the actual qvw that I am using.  I do not have any filters selected at all.  Strange.... 

Also, I compared the straight table data to the source data and the prior period numbers do not agree with the source data spreadsheet. 

Thank you,

Re: Show Today's Sales and Same Day Last Year's Sales

My laptop is out of battery so won't be able to help you before 10 hours. Hopefully someone else might be able to expand on my logic, if not I can help once I return home

Best,

Sunny

Partner
Partner

Re: Show Today's Sales and Same Day Last Year's Sales

Ok.  Thank you!