Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Folks,
I am new to Qlik and looking for ways to get the current quarter value in a set expression. I have following expression where I have hardcoded the values for the current quarter (Q1-2018 below).
=Num(Round(Sum({<[Fiscal Period]={'Q1-2018'}>} DISTINCT [Contractual TOV])/1000), '$#,##0K')
how can I get rid of hardcoded value and make based on current date / system date? I need to get it in the same format as hardcoded here.
Can it be done thru a variable let's vCurrentQtr that is calculated out of some date function that can be reused in other places/expression?
Thanks,
Gaurav
Check this out.All the below calculations are based on current date.
You mean to say..datewise sum?
I just want the CQ value Q1-2018 not to be a hardcoded one. It should come from some date functions... I don't know which function can help in it.
Depends on the fields in your model, do you have any quarter Field defined in model?
Can you share sample app?
No I just want it to based on current system date.
Try this
=Num(Round(Sum({<[Fiscal Period]={">=$(=Num(QuarterStart(Max([Fiscal Period]))))<=$(=Max([Fiscal Period]))"}>} DISTINCT [Contractual TOV])/1000), '$#,##0K')
Check this out.All the below calculations are based on current date.
try with this,i hope it will help to you.
sum({<OrderDate = {">=$(=Quarterstart(max(OrderDate)))<=$(=max(OrderDate))"}>}Sales)
May be this?
Num(Round(Sum({<[Fiscal Period]={'$(=minstring({<[Fiscal year]={"$(max([Fiscal year]))"}>} [Fiscal Period]))'}>} DISTINCT [Contractual TOV])/1000), '$#,##0K')
I think this is what I was looking for:
=Num(Round(Sum({<[Fiscal Period]={$(=chr(39)& 'Q' & CEIL(MONTH(Today())/3) &'-'& YEAR(Today())&chr(39))}>} DISTINCT [Contractual TOV])/1000), '$#,##0K')
It fixes the issue and gives CQ based on system date.
Thanks,
Gaurav