Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
Your expression should be
=count({$<stock_week={"$(=min(stock_week))"}>} total scan_id)
Your expression should be
=count({$<stock_week={"$(=min(stock_week))"}>} total scan_id)
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.
The same for =count({<stock_week={'sale_week'}>} scan_id)/min({<stock_week={'sale_week'}>} stock_added)
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
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)