Comparison vs 7 days ago

[KJM]

Hi there,

We have a table with our total order determined by 

Sum(Order_Split_Decimal) + Sum(Order_Case_Qty)

which is split by the [Order_Date]

We now want to compare this with the value for the same day of week last week.  I have tried created a set expression to determine the [Order_Date] minus 7.  

When I use 

=Date(Order_Date-7)

in a new column it is correctly showing the date for 7 days ago.

However, I have tried several variations of the following set expression and none seem to work:

Sum({$<Order_Date={"$(=Date(Order_Date-7))"}>} Order_Split_Decimal) + 
Sum({$<Order_Date={"$(=Date(Order_Date-7))"}>} Order_Case_Qty)

I assume I can't use a variable as the table will show several [Order_Date] values (one per record/row). 

Appreciate any ideas,

Thanks,

K.

[brunobertels]

Hi 

Can you try this 

Sum({$<Order_Date={"$(=Date(Order_Date)-7)"}>} Order_Split_Decimal) + 
Sum({$<Order_Date={"$(=Date(Order_Date)-7)"}>} Order_Case_Qty)

[KJM]

Thanks for the response.  Unfortuantely that one doesn't work either.  

[marcus_sommer]

It's not a matter of the selections within the expression else of the dimension because the results of the 19/11 doesn't belong to the one of the 26/11.

Workarounds may be to use {1} and/or TOTAL within the expressions or aggr() + interrecord-functions or similar methods to bypass the dimension-matching (it could become complex with extra side-effects to the usability and performance).

Simpler would be to skip the dimension and using n dedicated expressions - maybe up to a dozen ones but not for longer tables.

Another alternatively would be to use The As-Of Table - Qlik Community - 1466130 to synchronize the dates to specialized field.

[Kushal_Chawda]

@KJM  with dimension as date itself , set analysis won't work because set doesn't evaluate for each individual value of dimension. You will need something like above function here with sortable aggr function. Make sure that your date is not missing any days (probably call the date from calendar where all the dates are available).

=sum(aggr(above(Sum(Order_Split_Decimal) + Sum(Order_Case_Qty)+sum({1}0),7),([Order Date],(NUMERIC))))