can we insert time( dynamic ) into set expression

[coloful_architect]

hi folks 

I have a  dataset to simplify like this.    For each product, it has sales and sales date. but it also has a check time

I want to have a line chart like as long as I  report a code with check time , the line chart only shows the data after that 

check time.

for example, if I check ABC, it only shows all numbers from Aug 22 and forward. same thing for ACD, it shows numbers

from July 22 and forward.

Product  Sku	Sales	Sales time	check time
ABC	$46	Jul-22	Aug-22
ABC	$47	Aug-22	Aug-22
ABC	$56	Sep-22	Aug-22
ACD	$291	Jul-22	Jul-22
ACD	$90	Aug-22	Jul-22

I tried to create a time variable of check time and tried this ...but no luck

Sum({1 <Sale time= { '$( >$(check Time) )' }>} [sales])

any advise ?

Thanks

[RafaelBarrios]

hi @coloful_architect 

I don’t see a date time field, but I guess that this is what you are looking for 

Sum({1 <Sale time= {">$(check Time)"}>} [sales])

note that when you need to do evaluation in a field >, >=, <,<=, you need to use double quotes

If you still have some troubles, please paste the variable definition.

best,

[coloful_architect]

hi Rafael, applicate your response.

so yeah..I guess my questions has two layers. 

one is if I could insert time dimension  ( in this case , it is check Time)  directly into set expression. 

for example, at my line chart, the time (y axis) is still sale time, but the measurement is something like what you post. Btw, I try yours, for some reasons, it does not work.

The second is , since the check time field does not have a fixed time range, each code would have different check time, so I was guessing maybe setting the variable may help. 

so what I did was to set that check time as vCheck time ( I just assign that field of check time into variable). 

but it seems still not work...

any idea? 

[RafaelBarrios · Accepted solution]

Hi @coloful_architect 

lets try to find the problem.

can you please paste a filter with the Sale time values to check the date format

and also the check time variable definition, you should have an '=' sign in the begining

BTW you should be using [Sale time] and not Sale time

and if check time is variable, you should be using [check time]

Sum({1< [Sale time]={">$([check time])"} >} [sales])

also, remenber that 1 in "Sum({1<"  will ignore all your selections.

Best,

[RafaelBarrios]

reading once again, I just realized that check time is not a variable but a field.
in this case that will only work if you have a single value in that field using Only, max or min.

and if you are not going to use a variable, do the calculation inside $(=)

for example dv_datetime is a field

count({1< another_datetime_field={">$(=only(dv_datetime))"}>}id_delivery)

you can also go for possible values and use

Sum({1< [Sale time]=P([check time]) >} [sales])

https://help.qlik.com/en-US/qlikview/May2021/Subsystems/Client/Content/QV_QlikView/ChartFunctions/Se...

work the same in Qlikview and qlik sense.

Best,

i both cases, be sure to have the same format. 

[MarcoWedel]

maybe one solution could be:

Sum({$<SalesID={"=SalesTime>=CheckTime"}>} Sales)

loading this data:

tabSales:
LOAD RecNo() as SalesID,
     @1 as ProductSku, 
     @2 as Sales, 
     Date#(@3,'MMM-YY') as SalesTime, 
     Date#(@4,'MMM-YY') as CheckTime
FROM [https://community.qlik.com/t5/New-to-Qlik-Sense/can-we-insert-time-dynamic-into-set-expression/td-p/1985572] (html, codepage is 1252, no labels, table is @1) Where @1 like 'A*';