Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Looking for help in writing an expression. I’ve the below table in my qlik document.
Value | amount | date |
---|---|---|
A | 100 | 01/01/2018 |
A | 200 | 14/01/2018 |
A | 300 | 03/02/2018 |
A | 200 | 14/02/2018 |
A | 200 | 06/03/2018 |
A | 200 | 20/03/2018 |
now my requirement is when every user selects month in list it has to show current month and previous month value. For example if user select January in month it has to display previous month values as 0 (because there is no previous month) and current month as ‘300’ as below.
Value | previous month | current month |
---|---|---|
A | Null or 0 | 300 |
IF the user selects February i want to see the output as below
Value | Previous month | current month |
---|---|---|
A | 300 | 500 |
Message was edited by: Bharath Vikas Shanagonda I’m sorry I’ve updated my requirement. Instead of month column I will be using date column
May be this?
Where month_num created in script using below.
Num(Month(Date#(month,'MMMM'))) as month_num
Then, Create table
Previous Month
Sum({<month,month_num = {"$(=Max(month_num-1))"}>} amount)
Current Month
Sum({<month_num = {"$(=Max(month_num))"}>} amount)
PFA from Update?
Just updated the requirement Anil.
Didn’t have access to qlikview now as I’m using my mobile. Can you put the expression here please.
Expression is same, Script looks this?
LOAD *, month(Date#(date,'DD/MM/YYYY')) as month,Num(month(Date#(date,'DD/MM/YYYY'))) as month_num Inline [
Value, amount, date
A, 100, 01/01/2018
A, 200, 14/01/2018
A, 300, 03/02/2018
A, 200, 14/02/2018
A, 200, 06/03/2018
A, 200, 20/03/2018
];
thanks for that Anil, it is working fine and i've tested in straight table. Based on the selection it is giving me the result which I'm expecting. I'm giving the label name for an expression manually (current and previous) but I'm looking for an expression to change the label name dynamically.
For example if I select Feb month in the list box instead of previous the label name has to be Jan and for current it has to be Feb.
stalwar1 can you help me on this please.
You can use Label like
Prev Month
=Date(Max(month)-1,'MMMM')
Current Month
=Date(Max(month),'MMMM')