Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi guys,
I hope you are doing well! I am totally confused with flags.
Situation: I have three separate tables, for the argument sake, I uploaded a cut version of one. All three tables I merged with the col TimeStamp as I am using on date picker calendar i.e. one KPI is calculated based on one table, another, based on 2nd and third, based on the 3rd table. All three KPIs are on the same page and supposed to be changed at the same time as soon as a person selects, e.g. Last 7 days from the date picker. For this reason, I used Timestamp for of these tables.
My problem is that I cannot link the flags with the expression and calendar. Select the date in the calendar and nothing change in the KPI. What am I doing wrong? Is it timestamp? I have reviewed - I believe - all threads about it but did not find anything similar.
Here is the upload script:
TempCalendar:
LOAD Date(Floor(TimeStamp)) AS Date,
TimeStamp,
Hour(TimeStamp) AS Hour,
Minute(TimeStamp) AS Minute,
If(TimeStamp>=(Today()-30) AND TimeStamp<Today(), 1, 0) AS Is30DayPeriod,
If(TimeStamp=Week(Today()), 1, 0) AS IsCurrentWeek;
LOAD
Timestamp($(vMinDate) + (RecNo() - 1)/1440) as TimeStamp
AUTOGENERATE 1440 * $(vDays);
Gate:
LOAD
[idgate],
[TRACTOR_ARRIVE_DATE],
[TRACTOR_DEPART_DATE] as TimeStamp
FROM [lib://Desktop/gate_4.xlsx]
(ooxml, embedded labels, table is Sheet2);
Shift:
LOAD
idvessl,
EST_ARV_D As TimeStamp
FROM [lib://Desktop/vessels_3.xlsx]
(ooxml, embedded labels, table is Sheet1);
Here is the expression I used:
count({<IsCurrentWeek={1}}idgate)
Can you provide the xl data for your app?
Cool, so I figured it out. I might not respect the example I gave here as it was fake data, but I used a combination of flags, dates and set analysis.
if(IsToday = 1, count({1<[TimeStamp.autoCalendar.Date]={'$(=Date(today()-1))'}>} (CNTR_NUM)),
if(Is7DayPeriod =1, (count({ 1<[TimeStamp.autoCalendar.WeeksAgo] = {1}> } CNTR_NUM)),
if(IsCurrentMonth=1,(count({ 1<[TimeStamp.autoCalendar.MonthsAgo]={1} > } CNTR_NUM))
,' ')))
Hi Shahbaz, I figured it out. So, if a user selects 'Today' in the calendar, he will see the figures as of yesterday; 'This week', he will see the last week; 'This month', he will see the last month figures.
I fixed the calendar, just need to sort out the fact that the week starts on Sunday.
if(IsToday = 1, count({1<[TimeStamp.autoCalendar.Date]={'$(=Date(today()-1))'}>} (CNTR_NUM)),
if(Is7DayPeriod =1, (count({ 1<[TimeStamp.autoCalendar.WeeksAgo] = {1}> } CNTR_NUM)),
if(IsCurrentMonth=1,(count({ 1<[TimeStamp.autoCalendar.MonthsAgo]={1} > } CNTR_NUM))
,' ')))