Skip to main content
Announcements
Global Transformation Awards! Applications are now open. Submit Entry
cancel
Showing results for 
Search instead for 
Did you mean: 
amita1621
Contributor III
Contributor III

how to calculate percentage by comparing 2 months data

Hi All,

I am attaching excel file where i need to calculate % Change from Prior month so in excel its calculated using formula but i need same to be implemented in qlikview.

Formula in excel   =IFERROR(IF(ABS(C4/H4)>10,"NA",C4/H4-100%),0)

In qlikview i have brought both months data only % change is remaining , as of my way i have calculated % change also but how to implement NA as a condition in qlikview.

6 Replies
rubenmarin

Hi Amita, I think it should be something like:

If(Fabs(Field1/field2)>10, 'NA', (Field1/field2)-1)

amita1621
Contributor III
Contributor III
Author

I am using the below expression how can the above be used with this

Pick(Dim,

Sum([Amt $]/1000),

Num(If(Sum({<Month = {"feb'18"}>}[Amt $]/1000) <> 0 and Sum({<Month = {"jan'18"}>}[Amt $]/1000) <> 0,

Num(if(Sum({<Month = {"feb'18"}>}[Amt $]/1000)/Sum({<Month = {"jan'18"}>}[Amt $]/1000)-1)*100))

rubenmarin

Try with:

Pick(Dim,

Sum([Amt $]/1000),

Num(If(Sum({<Month = {"feb'18"}>}[Amt $]/1000) <> 0 and Sum({<Month = {"jan'18"}>}[Amt $]/1000) <> 0,

If(Fabs(Sum({<Month = {"feb'18"}>}[Amt $]/1000)/Sum({<Month = {"jan'18"}>}[Amt $]/1000))>10, 'NA',

Num(if(Sum({<Month = {"feb'18"}>}[Amt $]/1000)/Sum({<Month = {"jan'18"}>}[Amt $]/1000)-1)*100)))

amita1621
Contributor III
Contributor III
Author

i am using this

Pick(Dim,


Sum([Amt $]/1000),


Num(If(Sum({<Month = {"feb'18"}>}[Amt $]/1000) <> 0 and Sum({<Month = {"jan'18"}>}[Amt $]/1000) <> 0,


If(Fabs(Sum({<Month = {"feb'18"}>}[Amt $]/1000)/Sum({<Month = {"jan'18"}>}[Amt $]/1000))>100, 'NA',


Num(if(Sum({<Month = {"feb'18"}>}[Amt $]/1000)/Sum({<Month = {"jan'18"}>}[Amt $]/1000)-1)*100)))))


it doesnt show any resullt

amita1621
Contributor III
Contributor III
Author

in dimension i have used =Pick(Dim, Month, '% Change')

rubenmarin

can you upload a sample to make some tests?