Creating trendline including zeros

[dia2021]

Hi Everyone,

I am trying to create a trendline presenting data for sales each day. I have a column (sales) showing 0 or 1 depending on if the sales happened on that particular day (reportdate) and then broken down by categories, which category has been sold that day.

0 represents NO SALES, whereas 1 represents SALES. I am trying to capture if it is 1, then count the categories on that particular day (reportdate), and if it's zero, return it as 0 on the line chart.

My problem is how to capture the zeros for the line chart. I'm using this formula, but it is working only for the sales representing 1.   

count({<sales = {'1'}>}category) 

I want to count categories for 1 but not for zeros. If it's 0, I want to return it as zero. Is this possible to achieve?

My suggestions are greatly appreciated! 

Thank you.

[mikaelsc]

count({<sales = {'1'}>}category)  + 0 *count({<sales = {'0'}>}category) ?

[dia2021]

@mikaelsc  Thanks for the reply! I tried the way you have suggested above, but unfortunately second part of the expression is not working. Any other idea?

[mikaelsc]

and what is not working ? 

[dia2021]

@mikaelsc  Interesting. Let me try again and find out where the issue is. I'll get back to you. Thanks for your help!