Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi everyone,
A few months before and with a lot of help from the community i created a document.where more or less the user selects a day(trn_date) and through a button which gives value to a variable vdatetodate sees either the selected day's sold quantity vs the same weekday's last year quantity (vdatetodate=0) or the selected day's quantity vs the the same date's last year quantity (vdatetodate=1) .
selected date 03/03/2018
The set analysis I have used is :
pick (datetodate,
Sum({<trn_date = {"$(=TimeStamp(Max(trn_date-364), 'DD/MM/YYYY'))"}>} qty),//compares with same weekday last year
Sum({<trn_date = {"$(=AddMonths(Max(trn_date), -12))"}>}qty)//compares with exact same date last year
)
My problem is that when vdatetodate=1 I get no Qty for last year.This problem apeared only after 2017 ended.
(For example for any day of the 12/2017 works perfect).
I have checked and the data are correct!
Also my date as I insert it from sql server has the format for example 2018-03-03 00:00:00.000 ( I dont know if may this is the problem)
Can someone please suggest a solution ?
Is this the expression which isn't working?
May be you need date formatting for this also?
Sum({<trn_date = {"$(=TimeStamp(AddMonths(Max(trn_date), -12), 'DD/MM/YYYY'))"}>}qty)
Is this the expression which isn't working?
May be you need date formatting for this also?
Sum({<trn_date = {"$(=TimeStamp(AddMonths(Max(trn_date), -12), 'DD/MM/YYYY'))"}>}qty)
Thank you Sunny!
To be honest I tried it before but I was getting zeros...only after I copy and paste your answer worked.
Thank you again so much !!
May be you misplaced parenthesis... but glad it worked now.
Best,
Sunny