@Jesse How do I find max date for a date for each month and set a flag for each lax date for that month?
Can I use above functions to doit?
20230401 0
20230430 1
20230102 0
20230131 1
Hello, Thank you for the equations. However when I use these equations i got the error.
ifelse(truncDate(‘WK’, {order_date_time})= addDateTime(-1, ‘WK’, truncDate(‘WK’, now())), {net_sale}, 0)
The syntax of the calculated field expression is incorrect. Correct the syntax and choose Create again.
Appreciate your help
Hi @Anju - Can you try wrapping the date periods in double quotes? like “WK”
@Jesse To this what if I want to include only the first 3 days of a month instead of whole month?
@Jesse This topic worked great for me, thanks!
But I’m having troubles using a calculated field replacing the “sales” field, I can only apply this examples with a non calculated field. Any advices?
@Jesse Every time I try to use a aggregated calculated field instead of sales, it shows an error. Is there any way to use the aggregated calculated field in this formula?
Eg: ifelse(dateDiff({Date},now(),“MM”) = 0 AND {Date}<=now(), {CardCount}, 0)
CardCount = countIf({id},contains({type}, ‘CC’) OR contains({type}, ‘CF’) OR contains({type},‘PS’))
@IamPrat15 you would need to combine the logic into something like this:
ifelse(dateDiff({Date},now(),“MM”) = 0 AND {Date}<=now() AND (contains({type}, ‘CC’) OR contains({type}, ‘CF’) OR contains({type},‘PS’)), id, null)
And then count that field (or change it to countIf instead of ifelse and adjust where the arguments go.
Net is cannot have conditions that are using row-level fields and return an aggregate field, or vice versa, so need to check all the conditions at row level at the same time, return the unaggregated value, and then count that.
Hi @DanielSousa - see my response just above. If you are trying to do this with aggregated measures, you need to combine the logic across both fields (your original calc and the PoP) before you aggregate.
Hey @Jesse Thanks for sharing all these formulas, very helpful!
I’m working in an analysis where I want to compare lead volumes from the latest complete two weeks. Meaning Last week vs Week prior last week.
To do so, I have created the formulas below:
- Last WK Lead Volumes
ifelse(truncDate(‘WK’, {Created Ts})= addDateTime(-1, ‘WK’, truncDate(‘WK’, now())), {Lead ID}, 0)
- WK Prior Last WK Lead Volumes
ifelse(truncDate(‘WK’, {Created Ts})= addDateTime(-2, ‘WK’, truncDate(‘WK’, now())), {Lead ID},0)
Then I was our team wanted to be able to identify if an advertiser has Started/resumed activities last week in relation to the week prior last week, so I’ve created the formula below:
- Started/Resumed Activities Last WK
ifelse(ifelse({Last WK Lead Volumes}=0,‘Inactive’,‘Active’)=‘Active’ AND ifelse({WK Prior Last WK Lead Volumes}=0,‘Inactive’,‘Active’)=‘Inactive’,‘TRUE’,‘’)
- Paused/Stopped Activities Last WK
ifelse(ifelse({Last WK Lead Volumes}=0,‘Inactive’,‘Active’)=‘Inactive’ AND ifelse({WK Prior Last WK Lead Volumes}=0,‘Inactive’,‘Active’)=‘Active’,‘TRUE’,‘’)
And this is how it looks like in a table (aggregation used in the visual was distinct count):
My question is - In the case highlighted in the screenshot, it always returns 1 when there is no lead data for the referred week, but also when there is only 1 lead collected in the week. Highlighted in red and green, you can see a case where the week prior had no lead data and last week there was 1 lead, so it actually ticks the box of ‘Started/Resumed Activities Last WK’.
Is there any way to make it return 0 when there was no lead data in the week referred, for example, the ‘1’ circulated in green in my screenshot, can we make it to return as ‘0’? Thanks.
Hi @jessica.lunelli rather than returning 0 in your first two calcs, try returning NULL. If you are doing a count distinct, if there is a 0 there, it will still count it as a value, whereas NULLs will not be counted. If you’re still having trouble I would suggest posting your question as a new post, and maybe even build an examples using QuickSight Arena (part of the Community Site) with some sample/clean data so others can build a solutions and share it back with you.
1 Like
Hi @Jesse !
I am trying to create a formula to calculate ToDate DistinctCount of IDs.
I want to create a table that shows for each date the distinct count of values up to that date.
Example attached.
Can you please help?
I think I need to use distinctCountOver, but not sure how to filter it to only include the dates up to each date.
Hi @rsallaii - I think essentially you are looking for a running distinct count, which isnt possible today. Since distinct counts are not an additive type of operation and we cant determine the value of a given cell by referencing the numbers in the other cells, it really needs to be recalculated (a unique query) for each cell, and the current calculated field functions dont allow for that. Honestly I think you are going to need to pre-calculated this in your SQL.
I see, thank you for confirming that.
Can you please clarify what is the use case for periodToDateDistinctCountOverTime(aggregated unionset, date, period )
and what should go in the first parameter “aggregated unionset”, when I click learn more it directs me to welcome page and I didn’t find documentation for this function.
Thank you.
Hey @Jesse ,
I am trying to create an analysis to check the diff of ID volumes (distinct count of IDs) from ‘Last 90 days volumes’ vs ‘90 days prior last 90 days volumes’
Which formulas would you use to calculate the distinct count of ‘90 days prior last 90 days lead volumes’?
Thanks
Hi @rsallaii - that function actually shouldnt have been in the UI. The product team is issuing a hotfix to remove it. Sorry for the tease. I agree it sounded promising but when I tried it and reached out to product with similar questions we found it was a bug having that function there. Maybe someday 
Hi @jessica.lunelli - you will use similar structure, just return the field you want to distinct count (the dimension). Something like this:
Last 90 days:
distinct_count(ifelse(dateDiff({order_date},now()) <=90, {field you want to count}, null)
Prior 90 days:
distinct_count(ifelse(dateDiff({order_date},now()) <=180 AND dateDiff({order_date},now()) >90, {field you want to count}, null)
1 Like
Thank you very much, @Jesse!
1 Like
@Jesse I’m trying to get YoY in my Visualization shown below;
I’m using the below calculated field:
Difference ( sum( {Spend $} ), [{date_key_month} DESC], 12 )
However I get the below error message:
“Arbitrary lookups in windows are unsupported for Mysql server versions below 8.0 and MariaDB server versions below 10.2. Upgrade the database engine to access this feature.” and the 12 in calculation is underlined in red.
I’m unsure what I’m doing wrong or if there is a better / different way of getting what I need?
How do I do this year’s week N vs. last year’s week N for the last 5 weeks?
