topic Re: Set analysis for max date value? in QlikView

Set analysis for max date value?

Wed, 12 Aug 2015 07:07:44 GMT

There is month name and week name for each date. when i select month i need the value for max date.

Ex- For August i need the value for max date(i.e 31st August) as in database

Thanks in advance,

Soumya

Re: Set analysis for max date value?

Wed, 12 Aug 2015 07:11:18 GMT

We have 4 columns

Month,Week , Date and some metric

i tried this way

=Sum({<Date = {"$(max(date))"}>}listing_count)

But its not working

Can anyone help me out of this?

Re: Set analysis for max date value?

Anonymous — Wed, 12 Aug 2015 07:14:51 GMT

one is Date, other is date

next "=" is missing

Date is an inbuilt function, rename your fieldnames myDate instead of Date

wrong: =Sum({<Date = {"$(max(date))"}>}listing_count)

try = sum({<myDate={"=$(=max(myDate))"}listing_Count)

Re: Set analysis for max date value?

settu_periasamy — Wed, 12 Aug 2015 07:14:52 GMT

Hi,

Field value is case sensitive. check your field date or Date

You can try

=Sum({<Date = {'$(=max(Date))'}>}listing_count)

Re: Set analysis for max date value?

jonathandienst — Wed, 12 Aug 2015 07:16:06 GMT

Firstly - QV field names are case sensitve, so the fields date and Date are two distinct fields, which I assume is not your intention. Assuming the field name is "date" and that "date" is a proper QV date value and is formatted in your default date format:

=Sum({<date = {"$(=Date(max(date)))"}>} listing_count)

Re: Set analysis for max date value?

Wed, 12 Aug 2015 07:23:05 GMT

Actually its Date only

Type mistake

Re: Set analysis for max date value?

settu_periasamy — Wed, 12 Aug 2015 07:30:04 GMT

May be you can try with date format..

=Sum({<Date = {'$(=Date(max(Date)))'}>}listing_count)

=Sum({<Date = {'$(=Date(max(Date)),YourDateFieldFormat)'}>}listing_count)

Re: Set analysis for max date value?

Wed, 12 Aug 2015 08:31:54 GMT

sum({<myDate={"=$(=max(myDate))"}listing_Count)

This code is working but if i do the selection then it shows only the max date i.e august

I want Number for all the months

Thanks,

Soumya

Re: Set analysis for max date value?

Wed, 12 Aug 2015 08:44:17 GMT

sum({<myDate={"=$(=max(myDate))"}listing_Count)

This code is working but if i do the selection then it shows only the max date i.e august

I want Number for all the months

Thanks,

Soumya

Re: Set analysis for max date value?

Anonymous — Wed, 12 Aug 2015 08:52:32 GMT

I donot understand with your origin Statement that you Need max date?

does that mean if I select August you want complet August, if I select Jun-Aug

you want data from 1.6.-31.8?

you would not Need set Analysis as you select the months you require

sum(listing_Count)

if you Need complet range you may use this

sum({<myDate={">=$(=min(myDate))<=$(=max(myDate))"}listing_Count)

Re: Set analysis for max date value?

Wed, 12 Aug 2015 09:05:00 GMT

If i dont do any selection it shows metric number for only the max date

i want the metric number for all the months

Re: Set analysis for max date value?

Wed, 12 Aug 2015 09:10:19 GMT

If i dont do any selection it shows metric number for only the max date

i want the metric number for all the months

Re: Set analysis for max date value?

Anonymous — Wed, 12 Aug 2015 09:13:07 GMT

an you post sample qvw?

Re: Set analysis for max date value?

Wed, 12 Aug 2015 09:13:21 GMT

For example i have date for july ,august and sep

if i dont selection any of these 3 months it showing the metric value for max date of sep month

But i want metric value for all the 3 months

Re: Set analysis for max date value?

Wed, 12 Aug 2015 09:20:13 GMT

Here is the sample file

Re: Set analysis for max date value?

Anonymous — Wed, 12 Aug 2015 09:25:52 GMT

can you please test with a textobject?

insert: = min(mydate) & '-' & max(mydate)

if july, August and sep are shown inwhite (=available) in a listbox the formula should Show the amount

forumla was not correct, did Show error, this forumla works in my example

sum({<myDate={">=$(=min(myDate))<=$(=max(myDate))"}>}listing_Count)

Re: Set analysis for max date value?

Wed, 12 Aug 2015 09:36:11 GMT

But your expression gives wrong data

sum({$<my_date={"$(=max(my_date))"}>}Metric) this expression gives me correct answer

in this above expression if i don't select anything its giving me sep 6 and the metric value 375

But i want July - metric value

August - metric value

Sep - metric value

Re: Set analysis for max date value?

sasiparupudi1 — Wed, 12 Aug 2015 09:46:35 GMT

his code is working but if i do the selection then it shows only the max date i.e august

What selections are you talking about?

The max(my_date) returning 375 as metric for 9/6/2015

Re: Set analysis for max date value?

Wed, 12 Aug 2015 09:49:56 GMT

Correct by default its showing max date i e 9/6/2015 and the value is 375

But by default i need july (max date) - value (31st)

aug(max date) - value(29th )

Sep(max date) - Value(6th)

Re: Set analysis for max date value?

Anonymous — Wed, 12 Aug 2015 10:00:05 GMT

to clarify: you have data from different months. you Need fromeach month the value frommax(date) withinthat month?

then aggr function might be helpful (i am not sure if Syntax is correct)

aggr(sum({$<my_date={"$(=max(my_date))"}>}Metric),my_date)