Aggr() function with master measures when values are missing???

[EspenH]

I have created a master measure for this period and previous period sales:

Master measure: Sales_CurrentPeriod

Sum({<Year={$(=max(Year))}, Date={">=$(=Min(Date)) <=$(=Max(Date))"}, Date={"<=$(=Date(Today()))"} >} Sales)

Master measure: Sales_PreviousPeriod:

Sum({$<Year={$(=max(Year)-1)}, Date={">=$(=Min(AddYears(Date,-1))) <=$(=Max(AddYears(Date,-1)))"},Date = {"<=$(=Date(AddYears(Today(), -1)))"}>} Sales)

I have created a filter with a drill down dimension containing Year, Month and date. 

When the user choose a time periode like a year and one or several months the master measures above (Sales_CurrentPeriod and Sales_PreviousPeriod) calculates the chosen years sales and the sales for the same period the previous year. This is working fine.

In my data (table) I have a dimension for City.

Now i would like to create a KPI to find the max decline in sales between Cities between current and previous period, using the master measures Sales_CurrentPeriod and Sales_PreviousPeriod.

When i write the expression like:

min(aggr(Sales_CurrentPeriod - Sales_PreviousPeriod, Citiy))

I get the number for the City with the largest decline only where I have sales both in current and previous period. If I have a city where i have only sales in previous periode (which may be the biggest decline) the KPI does note show this, only the largest difference where i have sales in both periods.

I have tried to use alt() function like:

max(aggr(alt(Sales_CurrentPeriod,0) - alt(Sales_PreviousPeriod,0) , Citiy))

This is giving me the same result as the other expression.

It seems to be connected to the temp table created by the aggr() function when the master measure do not find any values in (the selected) current period. 

Is there a solution for this...?

[CELAMBARASAN · Accepted solution]

Hi @EspenH 

This is the behaviour of Aggr function. 

Where there is a set analysis is used inside the Aggr function then same set should be repeated in outside aggr as well.

Try any of the below

Solution 1: (repeat the sets)

min(
{$<Year={$(=max(Year)-1)}, Date={">=$(=Min(AddYears(Date,-1))) <=$(=Max(AddYears(Date,-1)))"},Date = {"<=$(=Date(AddYears(Today(), -1)))"}> + <Year={$(=max(Year))}, Date={">=$(=Min(Date)) <=$(=Max(Date))"}, Date={"<=$(=Date(Today()))"} >}

aggr(Rangesum(Sales_CurrentPeriod, -1 * Sales_PreviousPeriod), Citiy))

Solution 2: (ignore all selections)

min({1}aggr(Rangesum(Sales_CurrentPeriod, -1 * Sales_PreviousPeriod), Citiy))

Thanks

Celambarasan

[EspenH]

Thank you Celambarasan, this is very much appreciated!!!

When you use both of the sets outside the aggr() function I guess the sets in the master measures filter what sets should be used, right?

Thank you again!

[CELAMBARASAN · Accepted solution]

Hi @EspenH 

Yes, you are correct.

Regards

Celambarasan

[EspenH]

Hi Celambarasan

How can i find from which City the result are delivered?

Thanks! 

[CELAMBARASAN]

Hi @EspenH 

Have you tried FirstSortedValue? Please refer below link to understand more about it.

Don't get aggr()avated using aggr() - Qlik Community - 1464136

Regards

Celambarasan