Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
srujanaponnuru
Creator
Creator

expression help

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

1 Solution

Accepted Solutions
tresesco
MVP
MVP

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)

View solution in original post

11 Replies
Gysbert_Wassenaar
Partner - Champion III
Partner - Champion III

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.


talk is cheap, supply exceeds demand
srujanaponnuru
Creator
Creator
Author

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful
srujanaponnuru
Creator
Creator
Author

in the source file my field is Evntdt as below:

   

EVNTdtTransaction typeSumOfTXN

2016-09-01 00:60
AC3

2016-09-01 00:61
ABC5

2016-09-01 00:62
ABC6

2016-09-01 00:63
DEF6

2016-09-01 00:64
DEF7

2016-09-01 00:65
DEF7

2016-09-01 00:66
DEF8

2016-09-01 00:67
DEF8

2016-09-01 00:68
XYZ89

2016-09-01 00:69
XYZ9

2016-09-01 00:70
XYZ3

2016-09-01 00:71
XYZ23

2016-09-01 00:72
XYZ56

2016-09-01 00:73
XYZ7

2016-09-01 00:74
XYZ8

2016-09-01 00:75
XYZ4

2016-09-01 00:76
XYZ6

2016-09-01 00:77
XYZ3

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
MVP
MVP

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
Creator
Creator
Author

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

tresesco
MVP
MVP

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

srujanaponnuru
Creator
Creator
Author

Transaction typeTodayYesterdayLast 7 daysLast 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
MVP
MVP

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