topic Re: Help in set analysis in QlikView

Help in set analysis

ali_hijazi — Sun, 14 Aug 2016 05:26:31 GMT

Hello

I have a pivot table with Dimension is Month_Year

How can I pass the dimension value into the Set Analysis in order to give different date ranges on different rows of the report.

where my expression is count({<TRANS_DATE={">= START_DATE <=END_DATE"}>}CUSTOMER)

START_DATE would be 1 year back prior to dimension value and END_DATE is one day prior to month start of dimension value

hope I was able to clarify the requirement

please advise

Anonymous — Sun, 14 Aug 2016 06:57:27 GMT

Are you looking for set analysis expression for last year and lastmonth ?

For lastmonth:

count({$<TRANS_DATE={"$(=Date(AddMonths(Max(Month_Year), -1), 'MMM-YYYY'))"}>} CUSTOMER)

For Lastyear:

count({$<TRANS_DATE={"$(=Date(AddMonths(Max(Month_Year), -12), 'MMM-YYYY'))"}>} CUSTOMER)

For dates between last year and last month :

count({$<TRANS_DATE={">=$(=Date(AddYears(Max(Month_Year), -1), 'MMM-YYYY')<=$(=Date(AddMonths(Max(Month_Year), -1), 'MMM-YYYY')) )"}>} CUSTOMER)

count({$<TRANS_DATE={">=$(=Date(AddMonths(Max(Month_Year), -12), 'MMM-YYYY')<=$(=Date(AddMonths(Max(Month_Year), -1), 'MMM-YYYY')) )"}>} CUSTOMER)

ali_hijazi — Sun, 14 Aug 2016 07:31:39 GMT

No this approachDate(AddYears(Max(Month_Year), -1) is the same on each row

I want max(month_year) to take into consideration the value of month_year in dimension

kaushiknsolanki — Sun, 14 Aug 2016 09:39:05 GMT

Hi,

Try the expression given in below link.

Regards,

Kaushik Solanki