Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

rasmusnielsen
New Contributor III

Reduction of expression

Hello guys,

Pretty new to Qlik and I was wondering how the following expression might be reduce to a simpler form?

(Avg({$<fillingDate={">=$(=Date(today()-365,'YYYY-MM-DD'))"}, paymentType={"Private"}, productName={"Euro 95"}>}ppriceRefPrice) - Avg({$<fillingDate={">=$(=Date(today()-365,'YYYY-MM-DD'))"}, paymentType={"Private"}, productName={"Euro 95"}>}ppriceEur)) * 100

The point is that when retrieving ppriceRefPrice and ppriceEur, then they are limited to a interval of the last 365 days, payment type must be "Private" and productName must be "Euro 95". The final result is (avg(ppriceRefPrice) - avg(ppriceEur)) * 100.

Can this be written in a more readable form?

Thanks!

1 Solution

Accepted Solutions
JustinDallas
Valued Contributor II

Re: Reduction of expression

So in my script, I could do something like this:

LET vToday = Today();

Table:

LOAD *,

If( SaleDate > addmonths('$(vToday)',-12),1) as IsRolling365

    ;

LOAD

Rand() * 10000 AS SaleAmount,

Date(Today() + Floor(Rand()*720) - Floor(Rand()*720) ) as SaleDate

AutoGenerate 500;

EXIT Script

;

Here, I test the SaleDate to see if it's withing 365 days in the past.  If it is, my flag of 'IsRolling365' gets a 1, if not, it gets a null.  Then in my graph/table/callout, I can have something like this.

Sum({<Rolling365={1}>}SaleAmount)

So I don't have to mess with date formatting or that weird "equals-alligator mouth-parentheses-equals" thing you usually have to do when comparing dates.  The only draw back is that its "365 days from the last execution of the script" and not "within 365 days of the greatest date selected", but I find that rarely to be a problem.

You can find out more here where they have a magnificent calendar.

Rolling 12 months flag in master calendar script?

7 Replies
mov
Esteemed Contributor III

Re: Reduction of expression

Looks readable tome.

Check if in the "Main" part of the script the date format is set to 'YYYY-MM-DD'.  If it is, you don't need to specify date format in your expression, making it a bit shorter.  These parts would be:

=Date(today()-365)

instead of

Date(today()-365,'YYYY-MM-DD')

atkinsow
Valued Contributor II

Re: Reduction of expression

You could put the separate AVG expressions into 2 variables. 
Then it would be something like

=$(vAvgRefPrice)-$(vAvgEURPrice)*100

JustinDallas
Valued Contributor II

Re: Reduction of expression

Looks intimdating at first, but is very readable.  The only thing that I might do if I was already working in the dashboard and had time would be to have a IsRollingYear flag.  That way I could do away with the Date calculation part (because Dates are the Devil) and then I would just have this:

IsRolling365={1}

mov
Esteemed Contributor III

Re: Reduction of expression

Yes, flags are especially helpful for set analysis expressions.

rasmusnielsen
New Contributor III

Re: Reduction of expression

Thank you for your reply. Could you elaborate a little bit on this?

JustinDallas
Valued Contributor II

Re: Reduction of expression

So in my script, I could do something like this:

LET vToday = Today();

Table:

LOAD *,

If( SaleDate > addmonths('$(vToday)',-12),1) as IsRolling365

    ;

LOAD

Rand() * 10000 AS SaleAmount,

Date(Today() + Floor(Rand()*720) - Floor(Rand()*720) ) as SaleDate

AutoGenerate 500;

EXIT Script

;

Here, I test the SaleDate to see if it's withing 365 days in the past.  If it is, my flag of 'IsRolling365' gets a 1, if not, it gets a null.  Then in my graph/table/callout, I can have something like this.

Sum({<Rolling365={1}>}SaleAmount)

So I don't have to mess with date formatting or that weird "equals-alligator mouth-parentheses-equals" thing you usually have to do when comparing dates.  The only draw back is that its "365 days from the last execution of the script" and not "within 365 days of the greatest date selected", but I find that rarely to be a problem.

You can find out more here where they have a magnificent calendar.

Rolling 12 months flag in master calendar script?

rasmusnielsen
New Contributor III

Re: Reduction of expression

Ahh, I see. Thank you for your input. It is very enlightening

I agree on the weird "equals-alligator mouth-parentheses-equals" thing. It makes it very hard to read the expression, but in this way, it can be put away in a script that is easier to understand.

Community Browser