How can loop in calculated field?

[orangchan]

hello. I have a question but, I am not good at English. So the sentence can be awkward because you are using a translator.

I want to compare two periods in one graph . A parameter was used so that the dashboard viewer could freely set the period. At first, I tried to express it as a simple line graph, but found that I couldn’t set more than two periods on the X-axis. So, according to the length of the given period, D, D+1 … D+diff (periodstart,periodend) and the values corresponding to each date are created as a calculated field. However, it seems that the loop statement does not exist in the provided function. Is there any way to solve this?

[Max]

please take a look at this article. It should have what you are looking for.

Creating period comparisons with QuickSight's periodOver and periodTo functions

This article uses the SaaS-Sales Dataset that you can download as part of the QuickSight Author Workshop. We will update the data to the current date so your calculations are realistic. If you do not have a QuickSight account please follow the instructions in the Initial Setup. Instructions to create the dataset can be found in Exercise 1 of the Build your first dashboard. Create an analysis for the dataset. The following steps show you how to create visuals to show data from the current year,…

[orangchan]

Thank you for your reply. I read the articles you said and practiced it, but I don’t think I conveyed what I wanted properly. The article you provided explains how you can calculate and display the difference between a given period and a certain number of days before (Year, Quarter, Month, Day). What I want is not a difference between a period and a period, but a way to express a period and a period at the same time.
Both of these two periods can be set arbitrarily by the viewer, and even the two periods can overlap to some extent, such as 7/1 to 7/5 and 7/3 to 7/8. So, the way I thought of is to replace the two periods with D, D+1, D+2… to match the X-axis, and to draw a line graph of the values for each period on the y-axis, that is, period 1_value, period 2_value will. Of course, if the value of the two periods can be expressed simultaneously in another method, it does not matter if it is not this method. Could this be possible?

[orangchan]

I tried implementing it temporarily

make parameter : These can be adjusted arbitrarily by the dashboard viewer.
p1s(period1start), p1e(period1end), p2s(period2start), p2e(period2end)

calculated field :

combined_period : 
ifelse(
(dateDiff(${p1s},{date}) >= 0 AND dateDiff(${p1e},{date}) <= 0), dateDiff(${p1s},{date}), 
(dateDiff(${p2s},{date}) >= 0 AND dateDiff(${p2e},{date}) <= 0), dateDiff(${p2s},{date}), 
NULL
)

period1_value  :
ifelse(
(dateDiff(${p1s},{date}) >= 0 AND dateDiff(${p1e},{date}) <= 0), {value},NULL
)

period2_value  :
ifelse(
(dateDiff(${p2s},{date}) >= 0 AND dateDiff(${p2e},{date}) <= 0), {value},NULL
)

The result will look like this : Values are randomly written for example purposes.

visualize these calculated fields, It becomes group by with combined_period

then will look like this :

It’s crude, but I made the shape I wanted

next step, i want

1. I want to express the difference between the two columns. In the visualization object, it looks like the same row because it’s group by but, it’s not same row. is it possible?

2. it will not work properly if the two periods include the same day (for example, December 1st to December 5th, December 4th to December 8th).

Is there idea…?

[Wakana]

hi @orangchan

are you saying that you would like visualize the difference between period1_value and period2_value?

kind reagrds,
Wakana

[orangchan]

hi @Wakana , thank you for response!

oh! that’s right!! i want the difference between period1_value and period2_value

for example, the first row of this table :

i want

period1_value	period2_value	difference
20,387	39,738	-19,351

[Wakana]

hi @orangchan

if that is, why not creating calc field to get the difference and set into the same visual you created for the table of combined_period and each aggregated values?

kind regards,
Wakana

[orangchan]

@Wakana

Are you saying that calculations between different rows are possible in QS??

---

Assuming there is a table like this :

in this table

How do I configure the calc field
difference between the value of 2022-12-06(10) and the value of 2022-12-13(100)?

[duncan]

Hello @orangchan!

To achieve the above you could do the following:

sum(ifelse({date} >= 'start-target - date-range1' AND {date} <= 'end-target - date-range1', {value}, NULL)) - sum(ifelse({date} >= 'start-target - date-range2' AND {date} <= 'end-target - date-range2', {value}, NULL))