Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
hi,
i created a straight table containing fields like transacation type, today's transaction, yestreday tx count, last 7 days count, last 28 days count
i have given the dimension as transaction type and in expressions other 4.
for Todays tx i have written an expression like : =sum({<Day={"=Num(Max(Day))"}>}SumofTxn)
For yestredays i ahve written an expression like : sum({<Day={"=Num(Max(Day)-1)"}>}SumofTxn)
but for other two i am not getting how to write expression.
please help
Try like:
Today : =sum({<Day={"$(=Max(Day))"}>}SumofTxn)
Yestesrday : =sum({<Day={"$(=Max(Day)-1)"}>}SumofTxn)
Last 7 days : =sum({<Day={">$(=Max(Day)-7) <=$(=Max(Day))"}>}SumofTxn)
Last 28 days : =sum({<Day={">$(=Max(Day)-28) <=$(=Max(Day))"}>}SumofTxn)
Note: If you select any date field and wish to see corresponding result, you might have to pass that field in all the set expressions like:
Yestesrday : =sum({<Date, Day={"$(=Max(Day)-1)"}>}SumofTxn)
What kind of field is Day? Is it the day number of the month? Then it won't work if you select the first day of the month. Day 0 won't exist. If you have a date field you should probably use that instead:
=sum({<MyDate={"$(=Date(Max(MyDate),'MM/DD/YYYY'))"}>}SumofTxn)
=sum({<MyDate={"$(=Date(Max(MyDate)-1,'MM/DD/YYYY'))"}>}SumofTxn)
=sum({<MyDate={">$(=Date(Max(MyDate)-7,'MM/DD/YYYY'))"}>}SumofTxn)
=sum({<MyDate={">$(=Date(Max(MyDate)-28,'MM/DD/YYYY'))"}>}SumofTxn)
Adjust the date format string MM/DD/YYYY to the format of your date field.
Hi,
Its not working like that...
can u please suggest other solution. With Day field only.
Can you share the values which Day contains, So might we can way to root other
in the source file my field is Evntdt as below:
EVNTdt | Transaction type | SumOfTXN |
2016-09-01 00:60 | AC | 3 |
2016-09-01 00:61 | ABC | 5 |
2016-09-01 00:62 | ABC | 6 |
2016-09-01 00:63 | DEF | 6 |
2016-09-01 00:64 | DEF | 7 |
2016-09-01 00:65 | DEF | 7 |
2016-09-01 00:66 | DEF | 8 |
2016-09-01 00:67 | DEF | 8 |
2016-09-01 00:68 | XYZ | 89 |
2016-09-01 00:69 | XYZ | 9 |
2016-09-01 00:70 | XYZ | 3 |
2016-09-01 00:71 | XYZ | 23 |
2016-09-01 00:72 | XYZ | 56 |
2016-09-01 00:73 | XYZ | 7 |
2016-09-01 00:74 | XYZ | 8 |
2016-09-01 00:75 | XYZ | 4 |
2016-09-01 00:76 | XYZ | 6 |
2016-09-01 00:77 | XYZ | 3 |
like this i ahve till 2016-09-30 with different timings next to that.
while loading i converted
Load Date(date#(EVNTdt,'YYYY-MM-DD hh:mm'), 'YYYY/MM/DD hh:mm') as EVNTdt,
day(date(date#(EVNTdt, 'YYYY-MM-DD h:mm'), 'DD/MM/YYYY')) as Day
now in the table i need to pull
for Todays tx i have written an expression like : =sum({<Day={"=Num(Max(Day))"}>}SumofTxn)
For yestredays i ahve written an expression like : sum({<Day={"=Num(Max(Day)-1)"}>}SumofTxn)
please jhelp
Your time stamp is not being properly interpreted, in '2016-09-01 00:77' - 77 can't be mm (minute). Rather try to trim the date to get only the date part and try like:
Load Date(date#(left(trim(EVNTdt),10),'YYYY-MM-DD'), 'YYYY/MM/DD') as EVNTdt,
day(Date(date#(left(trim(EVNTdt),10),'YYYY-MM-DD'), 'YYYY/MM/DD')) as Day
i just gave the example random numbers . the data source file time to is correct.
Could you post a sample qvw explaining the expected output there?
Transaction type | Today | Yesterday | Last 7 days | Last 28 days |
i need a straight table with the above fields
in todays column i need sum of transactions so thats y i have written the expression for Today column as
=sum({<Day={"=Num(Max(Day))"}>}SumofTxn)
but for last 7 days i gave expression as
=sum({<Day={"=Num(Max(Day)-7)"}>}SumofTxn)
but i am getting all zeros in the table.
'Last 7 days' means - 7th day or all last 7 days - what do you expect?