Sales Expression

[Anonymous]

So I have a few measures that I would like to, I guess, merge to create a new one.  Basically I have a measure that gives total of all sales based on expression:

sum(aggr(sum(distinct(quantity*unit_price)), order_product_id)) +

SUM(aggr(sum({$<order_type={'refund'}>}distinct (refunded_product_discount_total/refunded_quantity*-1)*quantity), order_product_id))

I also have a date dimension that retrieves dates within last 60 days, with expression as follows:

if(floor([completed_date.autoCalendar.Date])>=num(today())-60 and floor([completed_date.autoCalendar.Date])<= num(today()),[completed_date.autoCalendar.Date],null())

Is there a way to create a new measure that would give me an overall sum of product sales within the last 60 days?  Thanks in advance

[dwforest]

Yes, Set Analysis.

You basically will put the filter into the Measures, like:

sum(aggr(sum({<[completed_date.autoCalendar.Date])={">=num(today())-60<=num(today())"}>}

see help:

https://help.qlik.com/en-US/sense/June2018/Subsystems/Hub/Content/ChartFunctions/SetAnalysis/set-ana...

You will need to do some adjusting as the left side of set analysis has to be a field, so you can use floor(date) and it takes practice to get used to the syntax.