vikrao Member

Doesnt the invoice date need to be there though? I cannot differentiate between months if that is not there. Thanks, Vik

I got a very large number of results from this query. Do I also need to add a distinct customer to this query?

Something like this then? select Customer,InvoiceDate, Count (CAST(DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,InvoiceDate)+1,0)) as DATE)) as OrderNum from ArTrnDetail where OrderType = 'ST' and ProductClass NOT IN('_FRT','_TAX','TAX2') and LineType <> '4' and Salesperson IN ('EPI','MGN','PH') Group By InvoiceDate, Customer…

I am using an older version of mssql that does not have EOMonth built into it. Any other way I can check this?

You may be right. I tried to convert those to Dynamic Date ranges when I moved it to Domo since I want the data to automatically update. Maybe I should create a query with dynamic date in MSSQL to cross check?

I got similar results to before. The data is showing about 100-125 customers more than when I pull the same data from mssql. I am trying to find out why I have more users than mssql showing with this query now. Maybe something to do with dates when I used Domo's inbuilt date functions? I used domo's inbuilt date functions…

Thanks for the quick reply @MarkSnodgrass ! I will try this out and reply to this thread shortly. Vik

Thanks Grant! This was very helpful.

Thanks Mark. This was very helpful.

This is the sql query if that helps Select count(distinct Customer) from CustomerData where InvoiceDate between '2021-07-01' and '2022-01-31'

Yes, that would be fair to say. I will try it out and update this thread if I can get it to work.

To explain more precisely, If I have 100 sample requests in January, I want to find out how many of those customers who requested samples converted to first orders in the next 15 days, 30 days, 45 days.

sorry for late reply. I am trying to find 'first orders from customers who have requested samples from us in the past'

What if my invoice date keeps changing? I collect data for this by month so I keep changing the invoice date that I look back at.

Thanks Mark. I was able to use the Rank and File tile to get some of the data that I needed. I will try splitting and joining the data in MagicETL.