Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

I need =P() for last year selection, not current year selection

Current Year:

(sum(if(wildmatch([GL Account],'5*','6*'),[Journal Debit Amount]))-sum(if(wildmatch([GL Account],'5*','6*'),[Journal Credit Amount])))

/

sum({1<[Top Income]={'Sales'},Company=P(),[Fiscal Period]=P(),[Fiscal Year]=P()>} ([Credit Amount]+[Debit Amount])* (-1))

Previous Year:

(sum({<[Fiscal Period]=P(),[Fiscal Year]={"$(=([Fiscal Year])-1)"}>}

if(wildmatch([GL Account],'5*','6*'),[Journal Debit Amount]))-

sum({<[Fiscal Period]=P(),[Fiscal Year]={"$(=GetFieldSelections([Fiscal Year])-1)"}>}

if(wildmatch([GL Account],'5*','6*'),[Journal Credit Amount])))

/

sum({1<[Top Income]={'Sales'},Company=P(),[Fiscal Period]=P(),[Fiscal Year]={"$(=([Fiscal Year])-1)"}>} ([Credit Amount]+[Debit Amount])* (-1))

I have the above 2 expressions that I am using to compare current year expense percentage of revenue vs last year expense percentage of revenue.

First off, I have the fiscal year set to always 1 selected value. Currently I have 2012 selected. In 2012 there are 4 companies (with current selection of expense) available. Last year there was only one for current selections of expense.

The above 2 formulas return the correct expense amounts for each year. However due to Company=P() part of last year revenue set I am pulling all 4 compaines saves vs the only 1 companies expenses. This is making the values different then they actually were.

Is there anyway to tell the set to take the possible Companies for the same selection, but assuming it was last year and not this year?

1 Solution

Accepted Solutions
philbishop
Contributor

I need =P() for last year selection, not current year selection

Have you tried:
Company= P({1<[Fiscal Year]={"$(=([Fiscal Year])-1)"}>} Company)

2 Replies
philbishop
Contributor

I need =P() for last year selection, not current year selection

Have you tried:
Company= P({1<[Fiscal Year]={"$(=([Fiscal Year])-1)"}>} Company)

Not applicable

I need =P() for last year selection, not current year selection

Yeah, I dug around the reference file to find the solution. I ended up using

Company=P({<[Category Description]=P(),[Fiscal Year]={"$(=([Fiscal Year])-1)"},[Fiscal Period]=P()>}Company)

Community Browser