Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I have the below expression. This expression returns me zero. Not sure where I am wrong.
=Count({<[link_claim_Claim_Status]={"*"} - {"CLO"},[Start Date] ={"<=addmonths($(varPreYearFullDateEnd),1)"}>} DISTINCT [ClaimID])
$(varPreYearFullDateEnd) has been assigned =Date('31/12/2010','DD/MM/YYYY'). I want to do the calculation for 31/01/2011 by adding 1 month.
On the other hand.
=Count({<[link_claim_Claim_Status]={"*"} - {"CLO"},[Start Date] ={"<=$(varPreYearFullDateEnd)"}>} DISTINCT [ClaimID]) just works fine.
returns me the correct value. I just want to add 1 month. I tried many ways where I will have date in the number format, use sum etc..finally asking the community to help.
Thanks in advance
Rajesh Vaswani
Hi Rajesh Vaswani,
you need a dollar sign expansion also for the addmonths function to call it within the set expression:
=Count({<[link_claim_Claim_Status]={"*"} - {"CLO"},[Start Date] ={"<=$(=addmonths($(varPreYearFullDateEnd),1))"}>} DISTINCT [ClaimID])
Regards,
Stefan
Hi Rajesh,
Stefan is right and using dollar sign expansion should do the trick. Although with these kind of requirement I generally prefer to load the master calendar table in the load script. I had attached an example of master calendar for your reference. If you look into the master calendar you will see I have MTD, WTD, YTD & previousYear etc. Also, if I am re-using same expressions within other objects then I would declare my expression as variables and use the dollar sign expansion. This approach helps me to maintin the calculations in one centralised location (Variable Overiew Window). And the re-usability & maintainence of these expression is easy. This is just like adding an abstraction layer.
I hope this helps!
Cheers - DV
Hi Stefan,
Thankyou for your reply. After $ expansion too it does not work. I am trying to figure out why. Let me know if you could suggest anything?
Thanks,
Rajesh Vaswani
The following expression seems to work
=Count({<[link_claim_Claim_Status]={"*"}-{"CLO"},[StartDate]={"<=$(varPreYearFullDateEnd+1)"}>} DISTINCT [ClaimID])
Accrding to me Variable should be assigned as:
varPreYearFullDateEnd= assign it : =makedate('2010','12','31')
Hi Rajesh Vaswani,
I noticed two issues:
with variable definition like
=Date('31/12/2010','DD/MM/YYYY')
I needed to remove the $() around the variable name when using the variable as argument to AddMonths function, i.e.
{<StartDate= {"<=$(=AddMonths(varPreYearFullDateEnd,1))"} >}
(I also struggle from time to time with variable evaluation).
It is also important, that there is no space between the smaller than (<=) and the dollar sign of the dollar sign expansion (I get zero as result otherwise).
As Erika wrote, I think a MakeDate would be clearer to understand what you want to achieve (assigning a date to a variable), so your expression should work as well.
In my test app, it works quite well, if you have any more problems, it may be useful if you could post a small sample application.
Regards,
Stefan