using if condition for aggr result

[muratmert41]

I would like to calculate the number of stores with stock. Here is the expression I have used so far, but it does not give the expected result:

Count(DISTINCT 
Aggr(
If(Sum({<operation_date= {'>=$(=vMaxTday) <=$(=vToday)'}>} total_stock) > 0, store_code),
store_code ))

vMaxTday and vToday are variables that define a date range from '2024-01-01' to today's date.

I am using this expression in a pivot table, and the table structure is as follows:

item_code

item_color

itemdim1

sales

returns

[rubenmarin]

Hi, try setting a table with store_code as dimension and that expression, check wich store_code are not counted as expected and that may give you a hint of what's happeing, if not, try posting the unwanted result so we can guess what's happening.

So far I would try removing the spaces in the date condition and using double quotes instead of single quotes.

Also setting the set analysis in the external count, it shouldn't have any impact in this expression, but I usually set the condtions to filter in both to reduce the data the process.

Count(DISTINCT {<operation_date={">=$(=vMaxTday)<=$(=vToday)"}>}
Aggr(
If(Sum({<operation_date={">=$(=vMaxTday)<=$(=vToday)"}>} total_stock) > 0, store_code),
store_code ))

 In expression editor you can also check that the variables are beign converted to the expected date values, I would also try to set fixed dates to confirm the date format that works with operation_date

[diegozecchini]

Count(
DISTINCT Aggr(
If(Sum({<operation_date={'>=$(=vMaxTday)<=$(=vToday)'}>} total_stock) > 0, store_code),
store_code
)
)