Announcements
cancel
Showing results for
Did you mean:
Contributor III

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

Hi at all,

I have a simple table like in the image.

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
Creator

Hi,

you can do it better in the load script.

Check this:

SalesW:

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)

4 Replies
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
Contributor III
Author

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

Thanks anyway

Creator

Hi,

you can do it better in the load script.

Check this:

SalesW:

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)

Contributor III
Author

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