topic Re: Comparing 2 alternate states in set analysis in QlikView

Comparing 2 alternate states in set analysis

ninnartx — Thu, 24 Nov 2016 08:47:16 GMT

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!!!

Re: Comparing 2 alternate states in set analysis

MK_QSL — Thu, 24 Nov 2016 08:52:08 GMT

Can you provide source data and how you have used alternate state here?

Re: Comparing 2 alternate states in set analysis

ninnartx — Thu, 24 Nov 2016 09:08:27 GMT

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.

Re: Comparing 2 alternate states in set analysis

MK_QSL — Thu, 24 Nov 2016 09:26:24 GMT

Where you want to display this?

However, I would like to be able to count the number of products in each category.

Re: Comparing 2 alternate states in set analysis

ninnartx — Thu, 24 Nov 2016 09:31:13 GMT

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

Re: Comparing 2 alternate states in set analysis

MK_QSL — Thu, 24 Nov 2016 09:40:49 GMT

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))