Total Not calculating when I utilize lag in calculated field beyond 1 time period

Q&A
, ,
1

Total Populates for below

(

(

(sumOver(sum({ifcis_instock_units}),

[channel,{time_period}]

)

)

/

(sumOver(sum({ifcis_ordered_units}),

[channel,{time_period}]

)

)

)

-

(lag

(

 sum({ifcis_instock_units}),

 \[{time_period} ASC\], 

 1

 , \[channel\]

)

/

lag

(

 sum({ifcis_ordered_units}),

 \[{time_period} ASC\], 

 1

 , \[channel\]

)

)

)

*10000

But does not populate for

(

(

(sumOver(sum({ifcis_instock_units}),

[channel,{time_period}]

)

)

/

(sumOver(sum({ifcis_ordered_units}),

[channel,{time_period}]

)

)

)

-

(lag

(

 sum({ifcis_instock_units}),

 \[{time_period} ASC\], 

 12

 , \[channel\]

)

/

lag

(

 sum({ifcis_ordered_units}),

 \[{time_period} ASC\], 

 12

 , \[channel\]

)

)

)

*10000

While Channel level data is publishing for both lags

2 
(((sumOver(sum({ifcis_instock_units}),[channel,{time_period}]))
/
(sumOver(sum({ifcis_ordered_units}),[channel,{time_period}])))
-
(lag(sum({ifcis_instock_units}), [{time_period} ASC], 1 , [channel])
/
lag( sum({ifcis_ordered_units}),[{time_period} ASC], 1, [channel])))
*10000

vs

(((sumOver(sum({ifcis_instock_units}),[channel,{time_period}]))
/
(sumOver(sum({ifcis_ordered_units}),[channel,{time_period}])))
-
(lag(sum({ifcis_instock_units}), [{time_period} ASC], 12 , [channel])
/
lag( sum({ifcis_ordered_units}),[{time_period} ASC], 12, [channel])))
*10000
3

Hi @premkup and welcome to the Quick Community!

Can you provide a bit more context on your situation and what you are trying to accomplish? I am a little unsure on what you are asking about.

4

Hi @premkup ,

Just checking back in since we haven’t heard from you in a bit. I wanted to see if the guidance shared earlier helped resolve your question, or if you found a solution in the meantime.

If you still have any additional questions related to your initial post, feel free to share them. Otherwise, any update you’re able to provide within the next 3 business days would be helpful for the community.

Thank you

5

Hi @premkup
I think the issue here is due to mixing aggregation levels in the calculation.

In your current formula, you’re using sumOver(...) for the current values, but inside the lag() function you’re using sum(...). These operate at different levels in QuickSight, which leads to inconsistent results or the calculation not working as expected.

To fix this, you should keep the aggregation consistent across the entire calculation. Since you’re already using sumOver, you should also use sumOver inside the lag() function.

Please try using this

(
(
    sumOver(sum({ifcis_instock_units}), [channel, {time_period}])
    /
    sumOver(sum({ifcis_ordered_units}), [channel, {time_period}])
)
-
(
    lag(
        sumOver(sum({ifcis_instock_units}), [channel, {time_period}]),
        [{time_period} ASC],
        1,
        [channel]
    )
    /
    lag(
        sumOver(sum({ifcis_ordered_units}), [channel, {time_period}]),
        [{time_period} ASC],
        1,
        [channel]
    )
)
)
* 10000