Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
muratmert41
Contributor
Contributor

using if condition for aggr result

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
Labels (1)
2 Replies
rubenmarin1

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
Specialist
Specialist

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