Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
gabriele_qlik
Contributor III
Contributor III

HOW CAN I DO AN EXPRESSION WHERE I COMPARE DATE??

Hi at all,

I have a simple table like in the image.

table.png

How can I create a field with a measure measure where I compare sales of present date with past date?

Sum(sales) / Sum({<Date = {"=Date - 1"}>}Sales) is not correct, in fact the result is 1.

Thank you at all

1 Solution

Accepted Solutions
captain89
Creator
Creator

Hi,

you can do it better in the load script.

Check this:

//load the previous date

SalesW:

LOAD *,

date(Date-1) as Date1

INLINE [

    Date, Sales

    01/01/2012, 9421

    02/01/2012, 8408

    03/01/2012, 2111

    04/01/2012, 1680

];

//map the previus date

map_sales:

mapping load Date, Sales resident SalesW;

//put the sales in the same row

Sales:

load *, ApplyMap('map_sales', Date1, 0) as Sales_1

resident SalesW;

drop table SalesW;

//then in the chart the formula is this one:

//sum(Sales)/Sum(Sales_1)

View solution in original post

4 Replies
christophebrault
Specialist
Specialist

Hi,

Try this :

Sum(Sales)/Above(Sum(Sales),1,1)

Above() function allow you to make reference to another line in a chart

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin
gabriele_qlik
Contributor III
Contributor III
Author

Yes, I know Above() function and I know this method. I would know if exist another way without Above().

Thanks anyway

captain89
Creator
Creator

Hi,

you can do it better in the load script.

Check this:

//load the previous date

SalesW:

LOAD *,

date(Date-1) as Date1

INLINE [

    Date, Sales

    01/01/2012, 9421

    02/01/2012, 8408

    03/01/2012, 2111

    04/01/2012, 1680

];

//map the previus date

map_sales:

mapping load Date, Sales resident SalesW;

//put the sales in the same row

Sales:

load *, ApplyMap('map_sales', Date1, 0) as Sales_1

resident SalesW;

drop table SalesW;

//then in the chart the formula is this one:

//sum(Sales)/Sum(Sales_1)

gabriele_qlik
Contributor III
Contributor III
Author

Thank you... If I work with load script I can compare date value.