Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
kkkumar82
Specialist III
Specialist III

Month Over Month , Quarter Over Quarter % Change in Cost

All,

I have materials and their cost which change month over month.

what I want is

1. I want to show trend of a material eg copper, the % change from previous month

2. The same for Quarter over Over

    For a Quarter , the last month cost in that Quarter has to be considered for showing % change in costs.

Attaching a sample with two materials cost, created a graph with copper which shows trend in the cost but I want the same for Cost Change difference %.

For eg

For Feb :   (Feb cost - Jan Cost)/ Jan Cost  * 100

Hope I am clear in my requirement

Thanks

Kumar

1 Solution

Accepted Solutions
sunny_talwar

If your data is sorted by date (which your attached sample seems to be, then try this expression in QV11.2 SR9:

(Sum({<material = {'Copper'}>}COST)/Aggr(Above(Sum({<material = {'Copper'}, Month>}COST)), Year, Month)) - 1

View solution in original post

12 Replies
sunny_talwar

1st question that I have for you is, do you have QV12? If you do, you would save yourself from a lot of hassle of doing some sorting in the script. Look here why -> The sortable Aggr function is finally here!

I am still not sure what you need for Q/Q, but for M/M you can use this expression (in QV12)

(Sum({<material = {'Copper'}>}COST)/Aggr(Above(Sum({<material = {'Copper'}, Month>}COST)), (Year, (NUMERIC)), Month)) - 1

Capture.PNG

You will see that the expression editor is showing error, but the result is what you would desire. I made a selection in the year because otherwise we were seeing a lot of lines which were difficult to interpret, but it will definitely work with all years

Capture.PNG

Additionally will also work if you make a selection for a particular Month (let's say we select Jun)

Capture.PNG

kkkumar82
Specialist III
Specialist III
Author

Hi Sunny,

I am using 11.20 SR9 .

1. Can we do anything from the script side for this

2. Coming to Quarter over Quarter, just like what have you done , if we select a year the graph should show % change between Quarters, here the logic is we will have three costs for a quarter so we have to take the last month COST for comparison.

Can you rewrite the expression which suits SR9 and explain a bit what have you done.

Thanks

Kiran Kumar

sunny_talwar

If your data is sorted by date (which your attached sample seems to be, then try this expression in QV11.2 SR9:

(Sum({<material = {'Copper'}>}COST)/Aggr(Above(Sum({<material = {'Copper'}, Month>}COST)), Year, Month)) - 1

kkkumar82
Specialist III
Specialist III
Author

Hi Sunny,

everything looks fine but is there a way that "Jan" month can also be shown by saying 0% , I tried with "Suppress zero values"

Thanks

Kiran Kumar

MayilVahanan

Hi

Try like this

Exp:

Alt((Sum({<material = {'Copper'}>}COST)/Aggr(Above(Sum({<material = {'Copper'}, Month>}COST)), Year, Month)) - 1,0)

Presentation:

Uncheck suppress zero values and check zero on bars.

Hope it helps

Thanks & Regards,
Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
kkkumar82
Specialist III
Specialist III
Author

Thanks Mayil for your suggestion , I acheived the 'Jan' one by writing a if condition

if(IsNull(Aggr(Above(Sum({<material = {'Copper'}, Month>}COST)), Year,  Month) - 1),0,

(Sum({<material = {'Copper'}>}COST)/Aggr(Above(Sum({<material = {'Copper'}, Month>}COST)), Year,  Month)) - 1)

Thanks

Kiran Kumar

sunny_talwar

Do you want to see a 0 or would you want to see a % comparison for last year's December month for the month of January?

kkkumar82
Specialist III
Specialist III
Author

Sunny,

Zero is fine, because 'Jan' is baseline for others. Any how thanks alot for the expression and follow up

Regards

Kiran Kumar

sunny_talwar

Awesome

Are you still looking for a Quarter on Quarter expression, or is that not a requirement?

Best,

Sunny