Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner - Master II
Partner - Master II

Help in set analysis

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

I can walk on water when it freezes
3 Replies
Anonymous
Not applicable

Hi

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)


or

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


ali_hijazi
Partner - Master II
Partner - Master II
Author

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

I can walk on water when it freezes
kaushiknsolanki
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi,

Try the expression given in below link.

Set Analysis with Max Date with Time-based Dimension

Regards,

Kaushik Solanki

Please remember to hit the 'Like' button and for helpful answers and resolutions, click on the 'Accept As Solution' button. Cheers!