topic Re: Set Analysis in QlikView

Set Analysis

bobbydave — Fri, 20 Nov 2020 18:02:30 GMT

I have data in years and quarters.

After 2015, some archiving needs to take place but before 2015, I don't need to touch anything

For archive workings, I need to only reference workings from Year = 2014 Quarter = Q3.

I know my set analysis is all over the place in the Archived workings.

//if Year is greater than 2015, workings + archived workings
if(Year > 2015,

//Workings for all countries
(1 - Count({<isPolicy={1}, CheckNo={7}, Validity.Status={N,I,R}, isApplicableToDataValidationComplianceCheck={'Y'}>} distinct Validity.Value)
/sum({<Accuracy.Status={N,I,R}>}[TIA policy count]))

+

// + Archived workings
sum ({< (1 - count({<isPolicy={1}, CheckNo={7}, Archived.Status={N,I,R}, Archived.isApplicableToDataValidationComplianceCheck={'Y'}>} DISTINCT Archived.Value) /sum({<Accuracy.Status={N,I,R}>}[TIA policy count])) } Year= {'2014'} Quarter = {'Q3'}),

//else, just do calculations as normal.
1 - Count({<isPolicy={1}, CheckNo={7}, Validity.Status={N,I,R}, isApplicableToDataValidationComplianceCheck={'Y'}>} distinct Validity.Value)
/sum({<Accuracy.Status={N,I,R}>}[TIA policy count]))

Question 1: Help with my Archive Set Analysis?

Question 2: Would it be better to put this set analysis in my loader, rather than as an expression as I would say this would be mighty tough to calculate for each Quarter and slow down the app.

Re: Set Analysis

maleksafa — Tue, 25 Aug 2015 08:31:58 GMT

i think you should archive in your load script and not set analysis, your document size will be smaller and the set analysis will be faster.

Re: Set Analysis

bobbydave — Tue, 25 Aug 2015 08:39:57 GMT

My loader loads all the files and then stored in a qvd but this dates from 2012.

Then I have an archived qvd which dates only for 2014.

So with a calendar, when I select 2015, this wont pull the data from 2014 which is why I was using set analysis to add the data referenced in 2014 Q3.

I understand I should put this into the loader but I am not sure how it will link with each other when the dates are different and with set analysis, I can explicitly say 'data from 2015, I need you to use the set analysis archived data from 2014 q3 and add these figures together.

Re: Set Analysis

bobbydave — Tue, 25 Aug 2015 10:05:49 GMT

Any idea what is wrong with my set analysis in the first place?