Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello All,
I have a situation where I need to carry over the totals from the previous month to the latest month.
let's say...
Month | March 2013 | March 2013 | ||
---|---|---|---|---|
Type | Item Type | A | B | Total |
Beginning | 200 | 0 | 200 | |
Sales | 400 | 500 | 900 | |
Loss | 800 | 100 | 900 | |
Total | 1400 | 600 | 2000 | |
So the contents are Quantity.. so the Total 2000 should carry over to the next month (April 2013)
Month | April 2013 | |||
---|---|---|---|---|
Type | Item Type | A | B | Total |
Beginning | 1400 | 600 | 2000 | |
Sales | 500 | 700 | 1200 | |
Loss | 850 | 150 | 1000 | |
Total | 2750 | 1450 | 4200 | |
Total will also carry over the next month and so on..
The data I have where sales and loss, items types are all coming from the database, while my beginning balance only comes from
an excel file that I need to merge to the database.
Are there any way I can achive this?
Thank you,
Donna
You can add a field to your calendar script as num(Month(Month)) as Month_Num and replace Month in the table expressions with Month_Num.
Modify the definitions for variables as follows:
1. vCurrentMonth
Definition: =num(Month)
2. vPreviousMonth
Definition: =if(num(Month)=1,12,num(Month)-1)
Use something like this expression for Current month
=num(avg({<Month_Num={$(vCurrentMonth)}>}MeanTotal)/100,'##%')
Use something like this expression for Previous Month
=num(avg({<Month_Num={$(vPreviousMonth)}>}MeanTotal)/100,'##%')
or try the following thread
http://community.qlik.com/thread/76227
Hope it helps
Siva