Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I just started to use QlikView recently.
Could you pls give me an advice about below expression? I want to show up rolling 12month's data.
=if(sum(if([YearMonth]> DATA(ReloadTime(),'YYYYMM') ()-12, [Cost])), sum(if([YearMonth]> DATA(ReloadTime(),'YYYYMM') ()-12, [Cost])))
Thank you so much for your help in advance.
FOR RELOAD TIME,
TRY THIS CODE
date(date#(ReloadTime(),'MM/DD/YYYY hh:mm:ss TT'),'YYYY-MM')
FOR ROLLING 12 MONTH TRY THIS CODE--
sum({$<Date = {">=$(=MonthStart(Max(Date),-12))<=$(=MonthEnd(Max(Date),-1))"},Year=,Month=>}Sale)
Thank you for your advice.
I'm sorry, I'm not good at to manage it. Could you pls let me know more details?
Here I add more info.
EX: Data base
YearMonth Notif Name Notif Cost
201205 12345 AAAAA 100EUR
201301 12345 AAAAA 200EUR
201302 12345 AAAAA 525EUR
201305 12345 AAAAA 300EUR
201201 11111 BBBBB 350EUR
201301 11111 BBBBB 250EUR
201305 11111 BBBBB 300EUR
and I want to show up on QlikView below style.
can you please explain the question in detail with example
Every month, cost date by each notification No. is updating.
Ex:
YearMonth | Notification No. | Notification Name | Cost(EUR) | |
201203 | 1000 | A | 700 | |
201211 | 1000 | A | 1000 | |
201302 | 1000 | A | 525 | |
201305 | 1000 | A | 800 | |
201306 | 1000 | A | 950 | Added on July |
201201 | 1001 | B | 350 | |
201301 | 1001 | B | 300 | |
201305 | 1001 | B | 850 | |
201306 | 1001 | B | 150 | Added on July |
… | … | … | … | |
|
|
|
| |
|
|
|
|
We would like to see the `sum` of the cost which are only over `Total 2,000EUR` by notification No. So I am using below expression.
=sum(if([Cost]>=2000,[Cost]))
And I would like to add one more condition which is rolling 12months.
Look at the Notification No `1000`, there are 5 different cost reports(=5 months).
However first one is little old data.
We just want to see if we open the QlikView on July 10th , each Notification No.’s cost total of rolling 12 months is calculate automatically without selecting YearMonth(201205~201306).
We want to see like below
Notification No. | Notification Name | Cost |
1000 | A | 3275 |
*Notification No. 1001 is not showing up, because Cost total is less than 2000EUR.
So based on ReloadTime, we would like to see the rolling 12month cost total.
Hope you understand what I mean...Thank you so much for your help!