27 Replies Latest reply: Dec 27, 2016 9:22 AM by Vasiliy Beshentsev

# Help with expression

Good day, everyone!

I have some throuble with expr. I need to get sum(Sales) over my filter.

On first page i got total sum, it work correct:

Sum({<t35PRDWAER = {'RUB'}>} DECNRPRDEC),

where t35PRDWAER field is currency, DECNRPRDEC is sales

On second page i want to get this, but it doesn't work:

Sum({<t35PRDWAER = {'RUB'}, BVRPBEDVTZ <= (VTRBEGDVTR + year - (365/BVRZAHWVTR))>} DECNRPRDEC),

where BVRPBEDVTZ is date of sale, VTRBEGDVTR is another date field, BVRZAHWVTR is something else.

Please, help me write expression correct.

UPD1: MY FALSE, FRIENDS, i forget to tell that i got 'YYYYMMDD' format in date fields!

UPD2: HERE IS EXAMPLE.QVF, take a look, friends!

• ###### Re: Help with expression

Sum(

{<

t35PRDWAER = {'RUB'},

BVRPBEDVTZ = {"<=\$(=Date((VTRBEGDVTR + year - (365/BVRZAHWVTR))))"}

>}

DECNRPRDEC),

• ###### Re: Help with expression

it particulary works, but not at the end! ,

can you please rewrite at this part  {"<=\$(=Date((VTRBEGDVTR + year - (365/BVRZAHWVTR))))"},

because Date() is convert all in () into date, but it incorrect. UPD1 in upperpost.

• ###### Re: Help with expression

Try

{"<=\$(=(VTRBEGDVTR + year - (365/BVRZAHWVTR)))"},

• ###### Re: Help with expression

it doesn't work

• ###### Re: Help with expression

provide sample data..

• ###### Re: Help with expression

Hi,

Refer Set Analysis: syntaxes, examples for more on set analysis.

• ###### Re: Help with expression

I tried to do like that, but it's doesn't work:

Sum(

{<

t35PRDWAER = {'RUB'},

date(BVRPBEDVTZ) = {<=\$(=(date(VTRBEGDVTR) + year - (365/BVRZAHWVTR)))}

>}

DECNRPRDEC),

• ###### Re: Help with expression

You cant use date(BVRPBEDVTZ) in set analysis.

Try BVRPBEDVTZ= {"<=\$(=(date(VTRBEGDVTR + year - (365/BVRZAHWVTR))))"}

• ###### Re: Help with expression

doesn't work ((

• ###### Re: Help with expression

Check the date format of field "BVRPBEDVTZ' and the value which you get from "=(date(VTRBEGDVTR) + year - (365/BVRZAHWVTR))". The format has to be same.

It would be great if you can share qvw with sample data.

• ###### Re: Help with expression

Friends! I convert all dates in main script!

CAST(vtr1.VTRBEGDVTR as date) as VTRBEGDVTR,

CAST(vtr1.BVRPBEDVTZ as date) as BVRPBEDVTZ

• ###### Re: Help with expression

Fields BVRPBEDVTZ & VTRBEGDVTR will be in date format, but need to confirm if "=(date(VTRBEGDVTR) + year - (365/BVRZAHWVTR))" returns value in same date format.

• ###### Re: Help with expression

what is year in the below?

VTRBEGDVTR + year - (365/BVRZAHWVTR)

• ###### Re: Help with expression

that right question, i thought already that this is function!

i need to minus 1 year from VTRBEGDVTR but how to write correct?

i wrote incorrect, sorry, friend

• ###### Re: Help with expression

use

or

• ###### Re: Help with expression

i replace this

• ###### Re: Help with expression

Hello, can someone help me now?

• ###### Re: Help with expression

Hi,

can you confirm what are you trying to acheive here? YTd, previous year??

(date(VTRBEGDVTR) + year - (365/BVRZAHWVTR)

Sum(

{<

t35PRDWAER = {'RUB'},

BVRPBEDVTZ = {"<=\$(=Date( AddYears(VTRBEGDVTR,1) - (365/BVRZAHWVTR) ,'YYYYMMDD' )}

>}

DECNRPRDEC),

• ###### Re: Help with expression

Hello!

Yes, previous year, update simplyfied condition here Re: Expression doesn't work (

• ###### Re: Help with expression

so is this resolved now?

• ###### Re: Help with expression

Well i guess yes, due to the fact that it was revising the terms of the problem (see new discuss)

• ###### Re: Help with expression

Maybe this is what you want?

Sum ({<t35PRDWAER = {'RUB'}, VTRBEGDVTR = {"=MakeDate(Year(VTRBEGDVTR), Month(VTRBEGDVTR), Day(VTRBEGDVTR))>=AddYears(MakeDate(Year(BVRPBEDVTZ), Month(BVRPBEDVTZ), Day(BVRPBEDVTZ)), -1)"}>} DECNRPRDEC)

• ###### Re: Help with expression

It works, but unfortunately shows incorrect sum. It's good solution to replace fields, but not in this case

Position of fields in condition is mean, i update simplyfied condition here, take a look Re: Expression doesn't work (

• ###### Re: Help with expression

Because i found a good idea in new topic so thats is solution:

i decided to make new field Flag which contains when payment make

so now i need to thing how to divide this two sum(sales) into 2 KPI in qlik sense: first year payments and next years

upd: and i find the decision

i use

1. Sum({<t35PRDWAER = {'RUB'}, FlagYearPayment = {'next'}>} DECNRPRDEC) for next years payments

2. Sum({<t35PRDWAER = {'RUB'}, FlagYearPayment = {'first'}>} DECNRPRDEC) for first year payments

still search for decision un expressions in qlik sense...