Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

cancel
Showing results for 
Search instead for 
Did you mean: 
rohitians
Creator III
Creator 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
Specialist
Specialist

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


View solution in original post

7 Replies
udit_kumar_sana
Creator II
Creator II

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
Creator III
Creator III

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
Specialist
Specialist

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
Creator III
Creator III
Author

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
Specialist
Specialist

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


View solution in original post

rohitians
Creator III
Creator III
Author

Hello,

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

Thanks

Rohit

anantmaxx
Specialist
Specialist

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.