Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

Not applicable

Current selection sales vs same period last year

Hi, I'm trying to compare sales from current selection vs same period -12 months. So if for example my current selection period is Oct 1st 2014 to Mar 31st 2015 I want to compare with Oct 1st 2013 to Mar 31st 2014. I have found following statements:

sum({$<[ProductType]={'Bananas'},CR.InvoiceDate={">=$(=yearstart(min(CR.InvoiceDate)))<=$(=max(CR.InvoiceDate))"}>}Ticket_Amt) for Current Period and

sum({$<[ProductType]={'Bananas'},CR.InvoiceDate={">=$(=yearstart(min(addyears(CR.InvoiceDate,-1))))<=$(=max(addyears(CR.InvoiceDate,-1)))"}>}Ticket_Amt) for previous period. Current Period works fine, however previous period keeps on giving 0 (there is sales for that period). What am I doing wrong? Thanks in advance for your help.

6 Replies

Re: Current selection sales vs same period last year

I think you need to add Date() function here:

Sum({$<[ProductType]={'Bananas'},CR.InvoiceDate={">=$(=yearstart(min(addyears(CR.InvoiceDate,-1))))<=$(=Date(max(addyears(CR.InvoiceDate,-1))))"}>}Ticket_Amt)

Not applicable

Re: Current selection sales vs same period last year

Hi Sunny, thanks for your fast reply. However still showing zero:-(

Re: Current selection sales vs same period last year

When you put these two expressions in a text box object

1) =yearstart(min(addyears(CR.InvoiceDate, -1)))

2) =Date(max(addyears(CR.InvoiceDate,-1)))

If these two give you the range your are looking for, in the format of your date field, then I don't see why it is not working. In my thinking, the formatting can be off.

Not applicable

Re: Current selection sales vs same period last year

These 2 give correct dates, but gives no sales. Actually now changed 1) to Date(min(addyears(CR.InvoiceDate, -1))) which should ensure that period is not taken from Jan 1st, but from 1st day of selection (-1 year) but both return no data for previous period (current period changed also and works fine. Also when I change selection to a year back, it returns data, so i just can't see why it's not working...

Re: Current selection sales vs same period last year

Have you also checked the format?? same format as CR.InvoiceDate is also very important. If they are not the same format, you will see a 0

Not applicable

Re: Current selection sales vs same period last year

As we're still in implementing / testing phase, I have loaded data for 28AUG2015 - 03SEP2015, and same period 2014.

When I do Textbox with min(CR.InvoiceDate) and loop through all dates, all dates have same format; (like 28 AUG 2014 12:00:00 AM). Also when I change my current selection to be 28 AUG 2015 to 28 AUG 2014, my current selection in both instances give me data, but previous period show 0 (which is logical for 2014 current selection, as there is no 2013 data, but for 2015 selection I would expect data for previous period).

Community Browser