-
Re: WTD,MTD & YTD info irrespective of filter selection
Hmm, I'm not terribly sure off the top of my head. I would start with something like this.
SUM( {1<MTD={1}>} InvoiceCharges )
The '1' before the alligator mouth tells Qlik that we want to look over the whole data set and where we have the property MTD set as a flag in our datamodel.
-
Re: WTD,MTD & YTD info irrespective of filter selection
Hi,
it will work only if he have flags on his tables.
otherwise, he will need to use fields like: Date field, Month and Year fields, MMYYYY or YYYYMM fields.. all this inside a set analysis.
So what do you have exactly on your model ?
-
Re: WTD,MTD & YTD info irrespective of filter selection
I dont have any Date filters in the set. something like this =Sum({}[Weekly_Rollover_Activity_Only.ROLLED_TO_AMT])/1000000 I wants to add the date condition inside this set
-
Re: WTD,MTD & YTD info irrespective of filter selection
Ok, my question was not clear enough maybe.
I want to know what is the Date fields you have on your model
-
Re: WTD,MTD & YTD info irrespective of filter selection
LET vDateStart = Timestamp(YearStart(Today()), 'YYYY-MM-DD hh:mm:ss.fff');
LET vDateEnd = Timestamp(YearEnd(Today()), 'YYYY-MM-DD hh:mm:ss.fff');
This wt i have created in the model. In order to pull the current year info.
Are you looking for this or something else?
Sorryyyyy
-
Re: WTD,MTD & YTD info irrespective of filter selection
No problem, I'm not looking for this.
if you want to make YTD/MTD etc it is time related analysis, so you should at least have a Date field on your model.
like this:
ROLLED_TO_AMT Date 120 01/01/2017 95 01/02/2017 -
-
Re: WTD,MTD & YTD info irrespective of filter selection
and did you create a master calendar using this field Process_date ?
-
Re: WTD,MTD & YTD info irrespective of filter selection
This is the auto generator calendar
[autoCalendar]:
DECLARE FIELD DEFINITION Tagged ('$date')
FIELDS
Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter', '$cyclic'),
Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$yearquarter', '$qualified'),
Dual('Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [_YearQuarter] Tagged ('$yearquarter', '$hidden', '$simplified'),
Month($1) AS [Month] Tagged ('$month', '$cyclic'),
Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth', '$qualified'),
Dual(Month($1), monthstart($1)) AS [_YearMonth] Tagged ('$axis', '$yearmonth', '$simplified', '$hidden'),
Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber', '$cyclic'),
Date(Floor($1)) AS [Date] Tagged ('$axis', '$date', '$qualified'),
Date(Floor($1), 'D') AS [_Date] Tagged ('$axis', '$date', '$hidden', '$simplified'),
If (DayNumberOfYear($1) <= DayNumberOfYear(Today()), 1, 0) AS [InYTD] ,
Year(Today())-Year($1) AS [YearsAgo] ,
If (DayNumberOfQuarter($1) <= DayNumberOfQuarter(Today()),1,0) AS [InQTD] ,
4*Year(Today())+Ceil(Month(Today())/3)-4*Year($1)-Ceil(Month($1)/3) AS [QuartersAgo] ,
Ceil(Month(Today())/3)-Ceil(Month($1)/3) AS [QuarterRelNo] ,
If(Day($1)<=Day(Today()),1,0) AS [InMTD] ,
12*Year(Today())+Month(Today())-12*Year($1)-Month($1) AS [MonthsAgo] ,
Month(Today())-Month($1) AS [MonthRelNo] ,
If(WeekDay($1)<=WeekDay(Today()),1,0) AS [InWTD] ,
(WeekStart(Today())-WeekStart($1))/7 AS [WeeksAgo] ,
Week(Today())-Week($1) AS [WeekRelNo] ;
DERIVE FIELDS FROM FIELDS [PROCESSED_DT] USING [autoCalendar] ;
You meant this?
-
Re: WTD,MTD & YTD info irrespective of filter selection
Ok, here with the calendar you have you can use the fields: Date, YearMonth for YTD MTD and WTD
Try this for the YTD:
Sum({<Date={">=$(=Yearstart(Max(Date)))<=$(=Max(Date))"} >}[ROLLED_TO_AMT])
Try this for the WTD:
Sum({<Date={">=$(=WeekStart(Max(Date))) <=$(=Date(Max(Date)))"} >}[ROLLED_TO_AMT])
Try this for the MTD:
Sum({<Date={">=$(=(MonthStart(Max(Date))))<=$(=Max(Date))"} >}[ROLLED_TO_AMT])
-
Re: WTD,MTD & YTD info irrespective of filter selection
Hi youssef,
I have tried.But its not working out
Below mentioned is the same
=Sum({<[Weekly_Rollover_Activity_Only.ROLLOVER_DESTINATION_TXT]={'RTF'},Date={">=$(=WeekStart(Max(Date))) <=$(=Date(Max(Date)))"}>}[Weekly_Rollover_Activity_Only.ROLLED_TO_AMT])/1000000
can u plz chk this let me knw?
-
Re: WTD,MTD & YTD info irrespective of filter selection
To go further you should send me sample data.
try to attach an excel file or a QVD with some rows of your data to take a look
-
Re: WTD,MTD & YTD info irrespective of filter selection
It's worked.I forgot to change the datefield
But it shows Till date information. But i don't want to show the incomplete week information
For example.
If i check the dashboard today it has to show till 4th March(i.e till sunday) for MTD,YTD & WTD.How to make that change?
Sorry for the trouble
-
Re: WTD,MTD & YTD info irrespective of filter selection
here we go:
Sum({<Date={">=$(=Yearstart(Max(Date)))<=$(=WeekEnd(Max(Date),-1))"} >}[ROLLED_TO_AMT])
Try this for the WTD:
Sum({<Date={">=$(=WeekStart(Max(Date))) <=$(=Date(WeekEnd(Max(Date),-1)))"}>}[ROLLED_TO_AMT])
Try this for the MTD:
Sum({<Date={">=$(=(MonthStart(Max(Date))))<=$(=WeekEnd(Max(Date),-1))"} >}[ROLLED_TO_AMT])
-
-
Re: WTD,MTD & YTD info irrespective of filter selection
Hi Yousef
YTD and WTD is working....
But MTD is throwing wrong data....Below is my formula
=Sum({<[Weekly_Rollover_Activity_Only.ROLLOVER_DESTINATION_TXT]={'RTF'},
[PROCESSED_DT.autoCalendar.Date]={">=$(=(MonthStart(Max([PROCESSED_DT.autoCalendar.Date]))))<=$(=WeekEnd(Max([PROCESSED_DT.autoCalendar.Date]),-1))"}>}
[Weekly_Rollover_Activity_Only.ROLLED_TO_AMT])/1000000
-
-
-
-
-
-
-
-
-
-
-
-
-
-
Re: WTD,MTD & YTD info irrespective of filter selection
I have not created any flag
How to get this done without a flag?
-