topic Re: Function within a Set Analysis in QlikView

Function within a Set Analysis

rmooney-jvchc — Wed, 25 Nov 2020 16:16:04 GMT

I'm new to using a set analysis and have watched a few videos, but am still having a problem getting this to work correctly. I'm using QlikView 12, and I'm wanting to get a count of values from a year prior to the date. Here is the formula I'm using:

Count({$<[Date]={"$(=addyears([Date],-1))"}>}[Date])

So I'm trying to get something that lists my Date, the Date a year ago, the count of values for the Date, and the count of values for a year ago from the Date. Any help as to what I'm doing wrong would be greatly appreciated.

Re: Function within a Set Analysis

avinashelite — Tue, 06 Jun 2017 13:58:46 GMT

Expression looks fine ...whats the error your getting ? could share the sample app ?

Re: Function within a Set Analysis

tajmohamed30 — Tue, 06 Jun 2017 13:58:50 GMT

have you tried set analysis builder app

Set Analysis Wizard for QlikView | qlikblog.at

Re: Function within a Set Analysis

avinashelite — Tue, 06 Jun 2017 13:59:35 GMT

Check whether you have data for that date in the last year ?

Re: Function within a Set Analysis

Anonymous — Tue, 06 Jun 2017 13:59:36 GMT

what's your date format?, are your dates dual() ?

Re: Function within a Set Analysis

sunny_talwar — Tue, 06 Jun 2017 14:01:00 GMT

Few things here

1) May be you are not selecting a single date, or even if you are, there is no harm in adding a Max() or Min() function based on the requirement

Count({$<[Date]={"$(=AddYears(Max([Date]), -1))"}>}[Date])

2) May be the date format set through environmental variables are different then the date format for your field Date. So, it might help to add Date() function with the correct date format for Date field

Count({$<[Date]={"$(=Date(AddYears(Max([Date]), -1), 'DD/MM/YYYY'))"}>}[Date])

3) Are you making selections in other date and time related fields such as Year or Month, it might make sense to ignore selections in those fields as Date will be driving your selection here

Count({$<[Date]={"$(=Date(AddYears(Max([Date]), -1), 'DD/MM/YYYY'))"}, Year, Month, MonthYear, Quarter, QuarterYear>}[Date])

Re: Function within a Set Analysis

rmooney-jvchc — Tue, 06 Jun 2017 14:15:29 GMT

I'm not getting an error, but all of my values are 0. I can replace the $(=addyears([Date],-1)) section with a static date and it returns the result I would expect. The .qvw file has sensitive data in it and I'm not sure how I could strip that out to share.

Re: Function within a Set Analysis

rmooney-jvchc — Tue, 06 Jun 2017 14:16:18 GMT

Date format is dd/mm/yyyy. Not sure what the dual() means.

Re: Function within a Set Analysis

rmooney-jvchc — Tue, 06 Jun 2017 14:16:50 GMT

I do have data, and as mentioned on my other reply, it returns the correct data when I enter a static date.

Re: Function within a Set Analysis

rmooney-jvchc — Tue, 06 Jun 2017 14:17:23 GMT

I appreciate the suggestions, but none of those worked. They still just returned zero values.

Re: Function within a Set Analysis

sunny_talwar — Tue, 06 Jun 2017 15:09:28 GMT

Would you be able to share a sample to show your issue?

Re: Function within a Set Analysis

rmooney-jvchc — Tue, 06 Jun 2017 15:33:10 GMT

Attached is a sample of the document/data I'm working on. It's the 4th column (2nd expression) I'm having problems with. Looking at that sample data, here is what I'm needing the first row to look like:

Date Date PY Count CY Count PY

6/6/2017 6/6/2016 98 103

Re: Function within a Set Analysis

sunny_talwar — Tue, 06 Jun 2017 15:45:17 GMT

Use The As-Of Table‌ here

Table:

LOAD Date,

[Count CY]

FROM

Book1.xlsx

(ooxml, embedded labels, table is Sheet1);

AsOfTable:

LOAD Date as AsOfDate,

Date,

'CY' as Flag

Resident Table;

Concatenate (AsOfTable)

LOAD Date as AsOfDate,

Date(AddYears(Date, -1)) as Date,

'PY' as Flag

Resident Table;