Calculate the date on wich a target is reached

[Jim-fr]

Hello,

l'm trying to show in a table the date wich the store first get 100 k€ of sales .

Datas

Sales,Date,sales

A,01/01/2022,90 k€

A,10/01/2022, 20 k€

A,01/02/2022,30 k€

B,05/01/2022,30k€

B,01, 01/03/2022, 80 k€

Result i want to get in a table : 

Store, Date 100 k€

A,10/01/2022

B,01/03/2022

I tryed this formula that worked quiet well if only on shop is selected : 

FirstSortedValue(Date,

aggr(if(rangesum(above(sales ),0,RowNo(TOTAL)))>100, Date),Date)
)

But when they are many sales in the table the date changes, it seems that the rangesum/above function work on the entire table and not only inside the aggr  cell scope.

Any ideas ?

[Oleg_Troyansky]

Four ideas for you:

- I think you need to aggregate sales with the sum() function. It might work accidentally if you always have one number per day, but generally speaking, sum() needs to be there

- You need to add Store to the list of your AGGR() dimensions, otherwise you get a so called "grain mismatch".

- Instead of FirstSortedValue, I'd use a simple Min() - you need the minimal Date that satisfies the condition. The function AGGR will return several dates, from the desired date and up, and you need the lowest of those.

- I think that TOTAL in the function RowNo() makes the AGGR to work on all Stores, instead of your desired store. I'd try removing it.

Your formula should look like  this:

Date(Min(

aggr(if(rangesum(above(sum(sales) ),0,RowNo()))>100, Date), Store, Date)
))

If you'd like to learn more about  advanced AGGR techniques, check out my session on AGGR and Set Analysis at the Masters Summit for Qlik - coming up in September in Madrid. See if you can attend:
https://masterssummit.com

Cheers,

[Jim-fr]

Hi Oleg and thank you for your answers and sorry for thanking you so late.

Inspired by your answer i achievd to do want i wanted this way (for information sales was a master item) : 

date(
min(
aggr(if( rangesum(above(sum({$<$(vSetDate), date_id=p({1} date_id)>}is_delivery), 0, rowno()))>100, date_id)
,drive_id, (date_id, (NUMERIC, ASCENDING)))
)
)

The only issue i have left is that i can't make it work when the user has selected a date, the set analysis don't work on dates in this case i don't know why.

[Oleg_Troyansky]

Hi Jim,

A few thoughts:

- To ignore the date selection, you can simply use this syntax:   Date_id = , with nothing after the equal sign.

- Not sure what do you have in the variable vSet Date, but keep in mind that Set Analysis filters are applied sequentially - first the vSetDate filter will get applied, and then the filter that ignores the date selection, will get applied.

Cheers,