Ignore Date selection in Aggregation

manojkulkarni — Thu, 12 Jan 2023 17:59:36 GMT

Hi Friends,

I have a scenario where in using the below expression for YTD. This is working fine in many cases but breaking in few.

While generating data for Q2 period YTD is not populating correctly. Upon investigation found that some of the transactions are not flowing beyond Q1 so our aggregation on account is failing. I would need this aggregation to get the right PercentageShare value for corresponding account.

Without aggregation, numbers are perfect but we will not get the proper PercentageShare respective for the account.

Sum(Aggr((
Sum({$<CalDt={"MaxDate))"}>} Value1)
-Sum({$<CalDt={"$(=date(YearStart(MaxDate)))"}>} Value2)
-Sum({$<CalDt={">=$(=date(YearStart(MaxDate)))<=$(=date(MaxDate))"}>}Value3)
)*PercentageShare, Account))

I would need your suggestion on how to ignore the date selection at Aggregation level or outer level but continue to use the inside as is.

Re: Ignore Date selection in Aggregation

rubenmarin — Sat, 14 Jan 2023 10:48:45 GMT

Hi, try adding a set analysis in the outer sum that includes all the data used in the aggr, like:

Sum({$<CalDt={">=$(=date(YearStart(MaxDate)))<=$(=date(MaxDate))"}>} Aggr((
Sum({$<CalDt={"MaxDate))"}>} Value1)
-Sum({$<CalDt={"$(=date(YearStart(MaxDate)))"}>} Value2)
-Sum({$<CalDt={">=$(=date(YearStart(MaxDate)))<=$(=date(MaxDate))"}>}Value3)
)*PercentageShare, Account))

topic Re: Ignore Date selection in Aggregation in QlikView

Ignore Date selection in Aggregation

Re: Ignore Date selection in Aggregation