Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
I need some help with alternate states and set analysis.
The alternate state is set up in a way that users can select 2 different periods and see how much their sales increased or decreased, as seen from the picture attached.
However, I would like to be able to count the number of products in each category.
For example, decreased = 3, increased = 2.
So I'm a little stuck trying to incorporate alternate state, aggr, and set analysis together.
Could someone provide me with a guideline pls?
Thank you!!!
For Decreased...
=SUM(Aggr(IF(sum({PeriodB<StoreCode =$::StoreCode>}SalesValue)-sum({PeriodA<StoreCode =$::StoreCode>}SalesValue)<0,1),Product))
For Increased..
=SUM(Aggr(IF(sum({PeriodB<StoreCode =$::StoreCode>}SalesValue)-sum({PeriodA<StoreCode =$::StoreCode>}SalesValue)>0,1),Product))
For No Change
=SUM(Aggr(IF(sum({PeriodB<StoreCode =$::StoreCode>}SalesValue)-sum({PeriodA<StoreCode =$::StoreCode>}SalesValue)=0,1),Product))
Can you provide source data and how you have used alternate state here?
Can't provide data source but basically
PeriodA sales = sum( {[PeriodA]<StoreCode =$::StoreCode>} SalesValue)
PeriodB sales = sum( {[PeriodB]<StoreCode =$::StoreCode>} SalesValue)
Diff = [PeriodB sales] - [PeriodA sales]
and there are 2 listboxes each for year and month assigned to Period A and B.
Where you want to display this?
However, I would like to be able to count the number of products in each category.
in another text or chart (straight table) object.
Like at the top of the dashboard there will be 2 huge numbers that say
# of products which sales increased = 2
# of products which sales decreased = 3
For Decreased...
=SUM(Aggr(IF(sum({PeriodB<StoreCode =$::StoreCode>}SalesValue)-sum({PeriodA<StoreCode =$::StoreCode>}SalesValue)<0,1),Product))
For Increased..
=SUM(Aggr(IF(sum({PeriodB<StoreCode =$::StoreCode>}SalesValue)-sum({PeriodA<StoreCode =$::StoreCode>}SalesValue)>0,1),Product))
For No Change
=SUM(Aggr(IF(sum({PeriodB<StoreCode =$::StoreCode>}SalesValue)-sum({PeriodA<StoreCode =$::StoreCode>}SalesValue)=0,1),Product))