Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

rohitians
Contributor III

Pivot chart PO details report

Hello,

I have purchase order pivot chart where in have difftrent item purcahse details  are displayed financial year wise I have to modify this report as a comparison report where in I have to introduce last year details also in current scenarion i have three expresion calculating rate,quantity,and value displayed only for a single year abut i have to displaye previous year details for thr three also how do i display the previous year's details with the existing also...

1 Solution

Accepted Solutions
anantmaxx
Valued Contributor

Re: Pivot chart PO details report

Quantity:

SUM({$< Year={$(=max(FinacialYear)-1)} ,DocumentType_Code={1}>}PurchaseLine_Qty)

and


Rate:

if(SUM({$<Year={$(=max(FinacialYear)-1)},DocumentType_Code{1}>}PurchaseLine_Qty)>0,AVG({<Year={$(=max(FinacialYear)-1)}>}Purchaseline_UnitCost),0)

also have you created a Financial Calender in your data model if yes then provide your financial year field


FinacialYear  if not then craete a finacial calendar in your data model.

Regards,

Anant


7 Replies
udit_kumar_sana
Contributor II

Re: Pivot chart PO details report

Hi,

You can try like this :

sum( {$<Year = {$(#=Only(Year)-1)}>} Sales )

returns the sales for the previous year in relation to current selection.

Regards,

Udit

pauljohansson
Contributor III

Re: Pivot chart PO details report

Hi,  It sounds like you should use the set analysis functionality. If you post your .qvw i might be able to help you,  br Paul

anantmaxx
Valued Contributor

Re: Pivot chart PO details report

add three more expression and when you you select the varios years it will show

previous year     Rate,Quatity,Value  along with current year Rate,Quantity,Value

=SUM({<Year={$(=max(Year)-1)}>}Rate)

=SUM({<Year={$(=max(Year)-1)}>}Quantity)

=SUM({<Year={$(=max(Year)-1)}>}Value)

Regards,

anant

rohitians
Contributor III

Re: Pivot chart PO details report

How can i introduce in the following code the year factor

Quantity:

SUM({$<DocumentType_Code={1}>}PurchaseLine_Qty)

Rate:

if(SUM({$<DocumentType_Code={1}>}PurchaseLine_Qty)>0,AVG(Purchaseline_UnitCost),0)


anantmaxx
Valued Contributor

Re: Pivot chart PO details report

Quantity:

SUM({$< Year={$(=max(FinacialYear)-1)} ,DocumentType_Code={1}>}PurchaseLine_Qty)

and


Rate:

if(SUM({$<Year={$(=max(FinacialYear)-1)},DocumentType_Code{1}>}PurchaseLine_Qty)>0,AVG({<Year={$(=max(FinacialYear)-1)}>}Purchaseline_UnitCost),0)

also have you created a Financial Calender in your data model if yes then provide your financial year field


FinacialYear  if not then craete a finacial calendar in your data model.

Regards,

Anant


rohitians
Contributor III

Re: Pivot chart PO details report

Hello,

Quantity code is working but the Rate code is not working yet..

Thanks

Rohit

Highlighted
anantmaxx
Valued Contributor

Re: Pivot chart PO details report

are you missing = after DocumentType_Code if first expression please check the below modified expression

if(SUM({$<Year={$(=max(FinacialYear)-1)},DocumentType_Code={1}>}PurchaseLine_Qty)>0,AVG({<Year={$(=max(FinacialYear)-1)}>}Purchaseline_UnitCost),0)

let m know if it works.