topic Re: Filter in visualization in App Development

Filter in visualization

Anonymous — Sat, 23 Jan 2016 12:32:28 GMT

Kindly help me in the below logic in the measure:

I have the below formula:

SUM(Total_Transfer)

I want the sum of only those whose Transfer date is maximum means MAX(Transfer_date).

Something like that:

SUM({$<Transfer_date = {MAX(Transfer_date)}>}Total_Transfer)..

But it is not working.

Thanks and Regards

Deepanshu

Re: Filter in visualization

sunny_talwar — Sat, 23 Jan 2016 14:58:52 GMT

May be this:

Sum({$<Transfer_date = {"$(=Date(Max(Transfer_date), 'YourDateFieldFormatHere'))"}>}Total_Transfer)

UPDATE: Max(Transfer_date) will return a number and to convert it to Date you will have to use Date function and also make sure that the formatting is also the same one as Transfer_date.

Re: Filter in visualization

swuehl — Sat, 23 Jan 2016 15:01:25 GMT

Max Transfer_date in the TOTAL context, right?

SUM({$<Transfer_date = {$(=Date(MAX(Transfer_date),'MM/DD/YYYY'))}>} Total_Transfer)

format the max date as Date using Date() function and using an appropriate format code ('MM/DD/YYYY' is just an example).

Re: Filter in visualization

sunny_talwar — Sat, 23 Jan 2016 15:18:36 GMT

Attaching a sample where I used a particular kind of formatting to display Transfer_date (MMMM DD, YYYY)

Script:

Table:

LOAD Date(MakeDate(2015, 1, 1) + Ceil(Rand() * 365), 'MMMM DD, YYYY') as Transfer_date,

Ceil(Rand() * 1000000) as Total_Transfer

AutoGenerate 10000;

And then the expression I used:

Sum({$<Transfer_date = {"$(=Date(Max(Transfer_date), 'MMMM DD, YYYY'))"}>} Total_Transfer)

HTH

Best,

Sunny

Re: Filter in visualization

Anonymous — Thu, 28 Jan 2016 17:51:59 GMT

Thanks a lot for your guidance!!

I am still getting error while using the formula. The date format is YYYY-MM-DD hh:mm:ss.

Attached is the snapshot of the formula I used.

Kindly help me!!

Regards

Deepanshu

Re: Filter in visualization

swuehl — Thu, 28 Jan 2016 18:48:26 GMT

What error message do you get? Have you tried to just press ok and check the result in your chart?

Re: Filter in visualization

sunny_talwar — Thu, 28 Jan 2016 19:24:52 GMT

Like Stefan said, I don't see where the error is? Are you seeing null or 0s?

Re: Filter in visualization

Anonymous — Thu, 28 Jan 2016 19:33:01 GMT

I am getting 0 value

Thanks

Deepanshu

Re: Filter in visualization

Anonymous — Thu, 28 Jan 2016 19:36:24 GMT

Yes!!.. I am working on Qlik sense.. On the below it shows OK.

But still I am getting 0 value...

Please guide

Thanks!!

Deepanshu

Re: Filter in visualization

sunny_talwar — Thu, 28 Jan 2016 20:28:07 GMT

Would you be able to share a sample?

Re: Filter in visualization

Anonymous — Thu, 28 Jan 2016 20:40:15 GMT

Sorry I cannot share the data or app due to some issue

I have attached the formula I am using in Qlik sense. Kindly check the formula

There are two upload dates : 2016-01-15 07:31:53 and 2016-01-21 10:15:07 with the Report_Type Filter M/W

I want the sum of only latest date for Report_Type W

Is the error because of the time format with date??

Kindly guide

Re: Filter in visualization

swuehl — Thu, 28 Jan 2016 21:04:14 GMT

What is

=Max(Uploaded_Date)

returning in a text box?

Maybe you need to use

$(=Date(Max({<Report_Type = {W}>} Uploaded_Date),'YYYY-MM-DD hh:mm:ss'))

Re: Filter in visualization

Anonymous — Sat, 30 Jan 2016 13:10:09 GMT

Thanks a lot sir!!

I apply the above formula as:

Sum({$<Uploaded_Date = {"$(=Date(Max({<Report_Type = {M}>}Uploaded_Date), 'DD-MM-YYYY'))"}>}[Total Transfer])

It works!!

Thanks

Deepanshu

Re: Filter in visualization

Anonymous — Tue, 02 Feb 2016 10:41:07 GMT

Hello Sir

I have attached my data set in form of tables

I use the below for formula:

Sum({$<Uploaded_Date = {"$(=Date(Max({<Report_Type = {M}>}Uploaded_Date), 'DD-MM-YYYY'))"}>}[Total Transfer]).

It takes the maximum upload date for Report_Type = M but somehow it takes the W also in consideration.

Can you please tell me where is the error

Please advice.

swuehl‌

sunindia‌

gwassenaar‌

Thanks

Deepanshu

Re: Filter in visualization

sunny_talwar — Tue, 02 Feb 2016 10:48:22 GMT

Try this:

Sum({$<Uploaded_Date = {"$(=Date(Max({<Report_Type = {M}>}Uploaded_Date), 'DD-MM-YYYY'))"}, Report_Type = {M}>}[Total Transfer])