Using total with count, set expression and date

[Giansonn]

I am trying to run an expression whereby I calculate the total given a set date.

The date I retrieve using min(stock_week) which works fine.

I then try to do:

=count({$<stock_week={'(min(stock_week)'}>} total scan_id)

This expression however gives me zeros see attached, why is this the case?

NOTE: I have also tried:

=count({$<date(stock_week,'DD/MM/YYYY')={"(date(min(stock_week),'DD/MM/YYYY'))"}>} total scan_id)

[kaushiknsolanki · Accepted solution]

Your expression should be

=count({$<stock_week={"$(=min(stock_week))"}>} total scan_id)

[Giansonn]

Thanks for your input Kaushik,

I have now gone into the data load to make sure that the dates are the same format but I am still having issues with dates elsewhere (I have now left this set expression and I am working on another one. This one was left unresolved).

These are the formats in the data load:

date(order_fixed_date - (WEEKDAY(order_fixed_date)-1),'DD/MM/YYYY') as sale_week

date(added_stamp - (WEEKDAY(added_stamp)-1), 'DD/MM/YYYY') as stock_week

Both variables are made so that they have a cast type of date in postgres before any SQL transformations.

These variables are the same in some observations (see attachment)

However when I do  =count({<stock_week=sale_week>} scan_id)/min({<stock_week=sale_week>} stock_added)

the chart tells me that there is not data because it contains only undefined values. Can you understand why that is the case? Again thanks for your support.

[Giansonn]

The same for =count({<stock_week={'sale_week'}>} scan_id)/min({<stock_week={'sale_week'}>} stock_added)

[Giansonn]

Okay I am fixing this using a flag:

in the dataload I am doing:

join(STR)
Load
scan_id,
If(stock_week = sale_week, 1, 0) as week_flag
Resident STR;

I don't think set analysis is made for dates so I would suggest doing everything in the data load

[Giansonn]

Hi Kaushik, again thanks for the answer, I am one step closer now.

Please see attached pictures. The formula is no longer broken however I am not seeing the right numbers.

These are the numbers I should see:

And these are the numbers that I now have:

I have also added the min stock week for the observation for greater clarity, I want to total scans that have the same stock week as min(stock_week)