topic Re: expression help in QlikView

expression help

srujanaponnuru — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: expression help

Gysbert_Wassenaar — Tue, 18 Oct 2016 07:52:39 GMT

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.

Re: expression help

srujanaponnuru — Tue, 18 Oct 2016 08:16:36 GMT

Hi,

Its not working like that...

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

Re: expression help

Anil_Babu_Samineni — Tue, 18 Oct 2016 08:47:52 GMT

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

Re: expression help

srujanaponnuru — Tue, 18 Oct 2016 09:16:34 GMT

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

Re: expression help

tresB — Tue, 18 Oct 2016 09:24:35 GMT

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

Re: expression help

srujanaponnuru — Tue, 18 Oct 2016 09:27:10 GMT

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

Re: expression help

tresB — Tue, 18 Oct 2016 09:31:33 GMT

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

Re: expression help

srujanaponnuru — Tue, 18 Oct 2016 09:41:29 GMT

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.

Re: expression help

tresB — Tue, 18 Oct 2016 09:47:05 GMT

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

Re: expression help

srujanaponnuru — Tue, 18 Oct 2016 09:47:42 GMT

all last 7 days

Re: expression help

tresB — Tue, 18 Oct 2016 09:53:43 GMT

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)