expression help

[srujanaponnuru]

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

[Gysbert_Wassenaar]

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.

[srujanaponnuru]

Hi,

Its not working like that...

can u please suggest other solution. With Day field only.

[Anil_Babu_Samineni]

Can you share the values which Day contains, So might we can way to root other

[srujanaponnuru]

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

[tresesco]

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

[srujanaponnuru]

i just gave the example random numbers . the data source file time to is correct.

[tresesco]

Could you post a sample qvw explaining the expected output there?

[srujanaponnuru]

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.

[tresesco]

'Last 7 days' means - 7th day or all last 7 days - what do you expect?