Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

gabriele_qlik
New Contributor II

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
Contributor

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

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)

4 Replies
christophebraul
Contributor III

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

Hi,

Try this :

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

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

gabriele_qlik
New Contributor II

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

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

Thanks anyway

captain89
Contributor

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

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
New Contributor II

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

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