Reduction of expression

[rasmusnielsen]

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!

[Anonymous]

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')

[Anonymous]

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

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

[JustinDallas]

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}

[Anonymous]

Yes, flags are especially helpful for set analysis expressions.

[rasmusnielsen]

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

[JustinDallas · Accepted solution]

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]

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.