I have two periods of time of different length within the same month. First period is 9 days and second is 8 days. Every day of each period is tagged with an acronym (ex: ED, PD, BL).
I want to calculate the delta of “total” between the days with the same tag (ex: ED1 - ED1, ED2 - ED2, etc).
I think the periodOverPeriodDifference function can’t help in this case as I’m not dealing with Day over Day or Month over Month etc.
Hi @Ales,
First off, I wanted to confirm that both periods/sets of data are pulled from the same dataset?
That would be key to have them interact with each other.
If so, you’ll most likely have to build a couple calculated fields to get this type of interaction.
Start by creating a denserank for your date field, partitioned by the {day} field. That way, you’ll be able to group by your acronym and determine which row is the later between the two dates.
Then, build out an ifelse statement that gathers your total for each:
ifelse(rank([{hit_day}], [{day}], ASC) = 1, {total}, NULL)
Then repeat for your second period total.
Last, create a calc. field for your delta:
sumOver({Rank2Total}, [{day}]) - sumOver({Rank1Total}, [{day}])
Something along these lines should be able to help with this case. Let me know if this works or if you have any questions
Thank you @Brett ,
yes, the dataset is the same. Also field {day} are actually two separate fields: {day_a} and {day_b}.
I’m trying the rank functions part but it returns syntax error. The following is accepted instead:
rank
(
[{hit_day} ASC],
[{day_a}]
)
I tried to add column rank A but it doesn’t return progressive numbers:
Hi @Ales,
Ah, understood! Ok, one other question; when you have two rows within a period that share the same ‘day’ acronym, do you want to sum those numbers? For example, PD1 in Period A?
Hello @Brett ,
thank you for catching that! Actually, that’s an issue with the data source. The answer to your question is ‘No’. For example, in case of PD1 in Period A, only May 6 and May 18 should be considered
Hi @Ales,
Ok sounds good! Then what if we tried something along these lines..
maxOver(ifelse({day A} = {day B}, {total}, NULL), [concat({day A}, " ", {day B})], PRE_AGG) - minOver(ifelse({day A} = {day B}, {total}, NULL), [concat({day A}, " ", {day B})], PRE_AGG)
Systematically, since there should only be two values for each ‘day’ acronym; this will take the max number (higher of the 2) and subtract the min (lesser of the 2) from the max.
Let me know if this works for your case or if you have any additional questions!
Thank you @Brett,
Seems like the {day A} = {day B}condition in the ifelse statement doesn’t find any match.
I’ve added a calculated field to confirm that:
ifelse(
{day A} = {day B},
"match",
"no_match"
)
I feel like this is somehow related to the {hit_day} field
Hi @Ales and apologies for missing your last response!
Ah gotcha, yes that makes sense actually since the ‘hit_day’ is different, each are on their own row so ‘Day A’ won’t have the opportunity to match ‘Day B’.
Another work around I had in mind would be creating a ranking based on the ‘Day A’ or ‘Day B’ value but as they are separate fields, that would not work either. After trying out a few different scenarios, since Day A and Day B are separate fields but feeding the same ‘total’ field, I don’t believe there’s a clear cut way to achieve this in QuickSight with the dataset structure and current limitations in place.
I can mark this as a feature request, but if you were able to build a work around that worked in this case, feel free to share for the rest of the community.
Let me know if you have any other questions.
Thank you
Hi @Ales,
Following up here as it’s been awhile since we last heard from you; while I’ve marked this as a feature request already, did you have any additional questions or comments to add?
If we do not hear back within the next 3 business days, I’ll close out this topic.
Thank you
1 Like