topic Hello, in QlikView

Hello,

Mon, 27 Apr 2015 13:52:16 GMT

Currently i am using the formula:

LET vNbInspectionECY2YearStowaways = 'sum({'&chr(36)&'<FACT_TYPE={' & chr(39) & 'INSPECTION' & chr(39) & '},DeficienceMainGroupCode={' & chr(39) & 'Stowaways' & chr(39) & '},Period_Path=,Period_Year = {">='&chr(36)&'(='&chr(36)&'(v_Last_Year))<='&chr(36)&'(=(max(Period_Year)))"} ,InspectionType={' & chr(39) & 'ECY' & chr(39) & '}>}NbInspection)';

giving me separte values for the previous year and the current year; but could you please help me use an " if "condition in this formula and use" DeficienceMainGroupCode" for one year and "DeficienceGroupCode" for the other year?

Re: Hello,

Peter_Cammaert — Mon, 27 Apr 2015 14:04:52 GMT

Define two expressions instead of a single, one with set modifier Period_Year = {'$(=max(Period_Year)-1)'} and another one with the simple set modifier Period_Year = {'$(=max(Period_Year))'}.

In the first expression, use the corresponding aggregation field (you didn't specify which goes where) and again in the second expression.

Peter

Re: Hello,

Tue, 28 Apr 2015 05:22:48 GMT

Hello Peter,

thanks for your response but could you plesae correct the below formula for me?
LET vNbInspectionECY2YearStowaways = 'sum({'&chr(36)&'<FACT_TYPE={' & chr(39) & 'INSPECTION' & chr(39) & '},DeficienceMainGroupCode={' & chr(39) & 'Stowaways' & chr(39) & '},Period_Path=,Period_Year = {'&chr(36)&'(=(max(Period_Year)))"} ,InspectionType={' & chr(39) & 'ECY' & chr(39) & '}>}NbInspection)';

Re: Hello,

Peter_Cammaert — Tue, 28 Apr 2015 06:45:16 GMT

I'm not going to, for a simple reason: the expression you posted has been awfully disfigured because your document uses variables in the expression field of charts. To create a new expression, you better start with testing the expression by entering it straight into the expression field of your chart. Afterwards you can still format the expression string so that it fits in a variable without undue $-substitution.

The two expressions can be:

=sum({$<FACT_TYPE={'INSPECTION'}, DeficienceMainGroupCode={'Stowaways'}, Period_Year = {'$(=max(Period_Year)-1)'}, InspectionType={'ECY'}>}NbInspection)

=sum({$<FACT_TYPE={'INSPECTION'}, DeficienceGroupCode={'Stowaways'}, Period_Year = {'$(=max(Period_Year))'}, InspectionType={'ECY'}>}NbInspection)

However, I'm almost certain that this will not produce the results you want. Not because the formulas are wrong (they're not) but because the logic is probably incorrect. You still didn't specify which field goes with what year. And do we select the same value in both DeficienceMazinGroupCode and DeficienceGroupCode to get a set in each case? Or do you want to create the two set selections and use them for both CY and LY, leading to four expression columns in all? In a pivot table?

Better post a screenshot of the chart you want to have.

Re: Hello,

Tue, 28 Apr 2015 07:04:10 GMT

Hello Peter,

i am currently using a pivot table:

dimension: period_year(its NOT caluculated dimension)

expression:

(name)= storeways

formula used: LET vNbInspectionECY2YearStowaways = 'sum({'&chr(36)&'<FACT_TYPE={' & chr(39) & 'INSPECTION' & chr(39) & '},DeficienceMainGroupCode={' & chr(39) & 'Stowaways' & chr(39) & '},Period_Path=,Period_Year = {">='&chr(36)&'(='&chr(36)&'(v_Last_Year))<='&chr(36)&'(=(max(Period_Year)))"} ,InspectionType={' & chr(39) & 'ECY' & chr(39) & '}>}NbInspection)';

so as result i am getting two coulmns for two years, with the corresponding values using the same expression;

but now i need to have the table in the same format except that previous year should use "DeficienceMainGroupCode" and the current year selcted in the application should use "DeficienceGroupCode".

could you plesae guide?

Re: Hello,

Peter_Cammaert — Tue, 28 Apr 2015 07:07:46 GMT

There are no other dimensions in this pivot table? So all you get is two cells with a value and two year headers?

Re: Hello,

Tue, 28 Apr 2015 07:35:45 GMT

No, the only dimension is "period_year"..but i have three columns:

one has the name of the event( ie: Stowaways etc), the next two has the corresponding values for event in the current year and the previous year.

Re: Hello,

Peter_Cammaert — Tue, 28 Apr 2015 07:43:49 GMT

Is the column with the Event Name then an expression as well? What does this expression look like?

Re: Hello,

Tue, 28 Apr 2015 08:08:15 GMT

it uses the valriable: vNbInspectionECY2YearStowaways

where:

i was wondering we if could modify the above like:

sum({$<FACT_TYPE={'INSPECTION'},Period_Path=,
Period_Year = {">=$(=$(v_Last_Year))<=$(=(max(Period_Year)))"} ,InspectionType={'ECY'},
DeficienceMainGroupCode={"if( Period_Year=$(=$(v_Last_Year)),
DeficienceMainGroupCode={'Stowaways'},DeficienceGroupCode={'Stowaways'})

>}NbInspection)

Re: Hello,

Peter_Cammaert — Tue, 28 Apr 2015 09:09:18 GMT

No.

Sets are assembled before the chart is recalculated. if (Period_Year... will return all years in Period_Year at the same time.
You cannot nest set analysis without E() or P().

That variable contains an expression to calculate sum(NbInspection). You said you have a column with the Event Name as well. Where does the Event Name come from in your pivot chart?

Re: Hello,

Tue, 28 Apr 2015 09:16:28 GMT

Hello,

could you plesae help me fix the formula:

sum

({$<FACT_TYPE={'INSPECTION'},Period_Path=,
Period_Year = {">=$(=$(v_Last_Year))<=$(=(max(Period_Year)))"} ,InspectionType={'ECY'},
DeficienceMainGroupCode={"=$<IF ( Period_Year=$(=$(v_Last_Year)),
DeficienceMainGroupCode={'Stowaways'},DeficienceGroupCode={'Stowaways'})>"}

>}NbInspection);

Re: Hello,

Tue, 28 Apr 2015 09:35:06 GMT

the types of events are calculated from expressions(example: the formula provided for Storeways, which is a kind of event.)

Re: Hello,

Peter_Cammaert — Tue, 28 Apr 2015 10:53:40 GMT

This is a bit confusing. First there were Event Names present in a separate column in your chart, now you are talking about Event Types that are calculated by the formula... Do you mean to say that you have a separate Pivot table for Stowaways (or is it Storeways?) and other Pivot tables for the other Event types?

Re: Hello,

Tue, 28 Apr 2015 13:36:11 GMT

currentlyi have a pivot table with:

dimension: period_year

expressions: (type of events)-> (ex: storeways, navigation etc) and one such formula used for this currently is:

so this will result in a pivot table with one column having the "type of events", the next two will be v_Last_Year and Period_Year respctively and these two columns will have the sum value for the corresponding "type of events".

ie. the same formula used to calculate the value for both the years, cause we are taking into consideration

:Period_Year = {">='&chr(36)&'(='&chr(36)&'(v_Last_Year))<='&chr(36)&'(=(max(Period_Year)))"},

but current requiremnt is for the "v_Last_Year", we need to use the filed: "DeficienceMainGroupCode"; but for "max(Period_Year), we need to use the filed: "DeficienceGroupCode";

so could you please suggest?

Re: Hello,

Peter_Cammaert — Tue, 28 Apr 2015 13:47:22 GMT

Can you post a screenshot of this particular pivot table in this discussion?

Re: Hello,

Tue, 28 Apr 2015 14:05:03 GMT

Re: Hello,

Peter_Cammaert — Tue, 28 Apr 2015 14:52:33 GMT

Ah, that makes a few things a lot clearer. AFAIK you do not need a pivot table since you can rotate a straight table 90 degrees and get the same effect.

Try with this formula. Put it straight in the expression field for 'Navigation'. You can keep the current content by commenting it out (put a double forward slash in front)

=sum({<FACT_TYPE={'INSPECTION'}, DeficienceMainGroupCode={'Stowaways'}, Period_Year = {'$(=max(Period_Year)-1)'}, InspectionType={'ECY'}> + <FACT_TYPE={'INSPECTION'}, DeficienceGroupCode={'Stowaways'}, Period_Year = {'$(=max(Period_Year))'}, InspectionType={'ECY'}>} NbInspection)

which will compose a set with records that either belong to LastYear and DeficienceMainGroupCode = 'Stowaway', OR belong to the CurrentYear and DeficienceGroupCode='Stowaway'. The LastYear selector doesn't use you v_Last_Year variable yet, but that can be changed later on.

Re: Hello,

Wed, 29 Apr 2015 08:59:32 GMT

Hello Peter,

that does make it simpler, thankyou; but we dont seem to be getting data for the previous year; there is a record for the current year only.