I'm having trouble using a variable!

[#1]

Hi! I'm new in Qlik Sense, I've been trying to do the following but I can't.

The date and date of cancellation is composed by YEAR-CAMPAIGN (YYYYCC)

The date of cancellation is registered on the date of the order (date) like the following

DATE	DATE OF CANCELLATION	ORDERS
201517	201518	6,029.00
201518	201519	3,211.00
201519	201520	3,370.00
201520	201521	4,513.00
201521	201522	4,094.00

but I need to do this:

DATE	DATE OF CANCELLATION	ORDERS
201517
201518	201518	6,029.00
201519	201519	3,211.00
201520	201520	3,370.00
201521	201521	4,513.00

I've been trying somethin like this:

DATE_OF_CANCELATION={#(=DATE)}

But is not working, could you please help me?

[phaneendra_kunc]

Not sure how DATE_OF_CANCELATION and DATE are related in your datamodel.

sample QVF might be helpful...

You can try this as a expression..

if(DATE_OF_CANCELATION = DATE, sum(ORDERS))

assuming both of those fields are in NUMBER format. if not use num(FIELD) as FIELD in your script.

Thanks,
Phani

[ramoncova06]

I would have to see the data, but couldn't you create a calculated dimension with DATE OF CANCELLATION = (DATE-1)

[undergrinder]

Hi Ramon,

Try this in load script:

date(date(yourdate)-1) as DATE_OF_CANCELATION

G.

[#5]

Thanks everyone for your help I've solved it with the redefinition of my data it was a better solution rather than using an expression.