topic Re: Set analysis for max Date in QlikView

Set Analysis for Max Date

nihhalmca — Sun, 15 Sep 2013 14:43:07 GMT

Hi All,

I need to show report account wise, sales for max (date) for instance

Account Date Sales

1 01-sep-13 100

1 02-sep-13 120

1 03-sep-13 130

2 05-Sep-13 150

2 06-Sep-13 160

3 08-Sep-13 140

3 09-Sep-13 110

3 10-Sep-13 100

4 11-Sep-13 200

4 12-Sep-13 290

4 13-Sep-13 280

Thanks in Advance

Nihhal

Re: Set analysis for max Date

Gysbert_Wassenaar — Sun, 15 Sep 2013 16:59:48 GMT

Use Account and Date as dimensions and as expression FirstSortedValue(Sales, -Date). Make sure Date is a numeric date field and not string values. Use the date# function if it's necessary to convert a string to a numeric date.

Re: Set analysis for max Date

Anonymous — Sun, 15 Sep 2013 19:02:59 GMT

Hey,

1. make sure that Date field stores a proper date (not a text), you can convert this text to date in a load script, example:

load

Account,

date(Date#(upper(Date),'DD-MMM-YY')) as Date,

Sales

inline [

Account,Date,Sales

1,01-sep-13,100

1,02-sep-13,120

1,03-sep-13,130

2,05-Sep-13,150

2,06-Sep-13,160

3,08-Sep-13,140

3,09-Sep-13,110

3,10-Sep-13,100

4,11-Sep-13,200

4,12-Sep-13,290

4,13-Sep-13,280

];

2. Create a Straight Table, add Account as Dimension and as expression add the following:

sum(aggr(if(Date=date(max(total <Account> Date)),sum(Sales)),Account,Date))

Best

Re: Set analysis for max Date

nihhalmca — Tue, 17 Sep 2013 07:20:07 GMT

Thank you for response,

Not working your logic

Re: Set analysis for max Date

nihhalmca — Tue, 17 Sep 2013 07:20:39 GMT

Thank you for response,

not working your logic

Re: Set analysis for max Date

Anonymous — Tue, 17 Sep 2013 13:07:20 GMT

Does work. You may need to adjust the example I gave you to your scenario/data model.

Re: Re: Set analysis for max Date

Anonymous — Tue, 17 Sep 2013 13:14:59 GMT

Example attached.

Re: Set analysis for max Date

nihhalmca — Wed, 18 Sep 2013 11:02:16 GMT

Thank you Karol Now working fine, i hope previously i might have done mistake

Re: Set Analysis for Max Date

nihhalmca — Thu, 27 Feb 2014 06:08:31 GMT

Thank You Karol

NiHhal.

Re: Set Analysis for Max Date

Anonymous — Thu, 27 Feb 2014 07:34:02 GMT

Hi Gysbert,

I have checked your expression and found that only Account dimension needed to add i think its not Date and Account both with FirstSortedValue() function or i am wrong?

anant