Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
ecabanas
Contributor II

Data Intersection Question

Hi

I'm creating an analysis as follows:

 

CustomerIDSalesidDate
C000001A31/01/2015
C000002B31/01/2015
C000003C31/01/2015
C000004D31/01/2015
C000001E28/02/2015
C000002F28/02/2015
C000005G28/02/2015
C000006H28/02/2015
C000005I31/03/2015
C000006J31/03/2015
C000007K31/03/2015
C000008L31/03/2015

When I select the month has to return to me the number of customers that bought the current month (selected) AND the previous month

2015-11-04_19h02_26.png

Many thank's guys

Eduard

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Data Intersection Question

1) Define your VMonth and VPriorMonth variables with a leading equal sign:

=CalendarMonthNumber-1

2) Since you selected in CalendarMonthName, you need to clear this field in the p() function to avoid an incompatible set:

count({<CalendarYear={'$(VYear)'},CalendarMonthNumber={'$(VMonth)'},CustomerID=P({<CalendarYear={'$(VYear)'},CalendarMonthNumber={'$(VPriorMonth)'}, CalendarMonthName= >}CustomerID)>} distinct CustomerID)

You can also remove the first to set modifier, since they won't change anything compared to your default set:

count({<CustomerID=P({<CalendarYear={'$(VYear)'},CalendarMonthNumber={'$(VPriorMonth)'}, CalendarMonthName= >}CustomerID)>} distinct CustomerID)

View solution in original post

8 Replies
Highlighted
MVP
MVP

Re: Data Intersection Question

There are some solutions for counting new / lost / returning customers here in the forum, for example:

New/Lost/Returning/Loyal Customers

Highlighted
ecabanas
Contributor II

Re: Data Intersection Question

Hi Swuehl,

First of all many thank's for your help , I read the link and I'm trying to do with this formula, but did not works, could you help to me?

count({<CalendarYear={'$(VYear)'},CalendarMonthName={'$(VMonth)'},CUSTACCOUNT

=P({<CalendarYear={'$(VYear)'},CalendarMonthName={'$(VPriorMonth)'}>}CUSTACCOUNT)>} distinct CUSTACCOUNT)

Many thank's for your patience

Eduard

Highlighted
MVP
MVP

Re: Data Intersection Question

I would start with checking

a) that each variable expands to a value and the value match field formats and possible values in fields

b) you are not selecting Jan as VMonth (I think your logic has a flaw at year Change)

c) that you are not selecting on other calendar fields than CalendarYear and CalendarMonthName, since this might render the set expression to an incompatible set (using the modified field values and selected field values).

All in all, it's hard to tell without knowing more details. Any chance you upload a small sample QVW?

Highlighted
ecabanas
Contributor II

Re: Data Intersection Question

Hi Swuehl

I created a doc and if you can help to me to create the formula. there is a table named did not work

Many thank's and sorry for the headache

Eduard

Highlighted
MVP
MVP

Re: Data Intersection Question

1) Define your VMonth and VPriorMonth variables with a leading equal sign:

=CalendarMonthNumber-1

2) Since you selected in CalendarMonthName, you need to clear this field in the p() function to avoid an incompatible set:

count({<CalendarYear={'$(VYear)'},CalendarMonthNumber={'$(VMonth)'},CustomerID=P({<CalendarYear={'$(VYear)'},CalendarMonthNumber={'$(VPriorMonth)'}, CalendarMonthName= >}CustomerID)>} distinct CustomerID)

You can also remove the first to set modifier, since they won't change anything compared to your default set:

count({<CustomerID=P({<CalendarYear={'$(VYear)'},CalendarMonthNumber={'$(VPriorMonth)'}, CalendarMonthName= >}CustomerID)>} distinct CustomerID)

View solution in original post

Highlighted
ecabanas
Contributor II

Re: Data Intersection Question

Hi Swuehl

Many mnay thank's the problem that i was working was to exclude the CalendarMonthName inside the function...but now I have new problem, when I want to compare january 2015 Vs 2014 this formula did not work, did you know how to solve?

Many many thank's again

Highlighted
MVP
MVP

Re: Data Intersection Question

You can create a MonthIndex field in your script like

LOAD

     Year,

     MonthNum,

     Year*12+MonthNum AS MonthIndex,

     ...

MonthIndex should now show consecutive numbers across all years, now operate on MonthIndex to find the previous month in your set analysis.

Highlighted
ecabanas
Contributor II

Re: Data Intersection Question

Many many thank's

If you come to Barcelona a Paella is waiting you 😉