topic Re: Show previous dates data in same row as date selected in QlikView

Show previous dates data in same row as date selected

Thu, 20 Dec 2012 16:17:33 GMT

I am trying to creat a table with one summary row;

Date, sales figure, Previous dates sale figure, Variance.

I am having trouble finding the previous dates sale figure to the one selected by the user on the calender.

Any advice greatly appreciated as I am new to Qlkiview

Re: Show previous dates data in same row as date selected

Gysbert_Wassenaar — Thu, 20 Dec 2012 16:36:45 GMT

You can probably use the above() function to get the previous dates sales figure: above(sales figure)

Re: Show previous dates data in same row as date selected

vikasgupta — Thu, 20 Dec 2012 16:42:41 GMT

Hi Gysbert

Plz I need your help bcz i did not get any solution for this plz click on following links:-

http://community.qlik.com/thread/68878?tstart=30

thanks

Vikas Gupta

Re: Show previous dates data in same row as date selected

Fri, 21 Dec 2012 08:44:59 GMT

Thanks for your reply unfortunately this doesnt work.

The calender allows just one date selected therefore only data for the date selected is brought back.

Regards

Re: Show previous dates data in same row as date selected

Gysbert_Wassenaar — Fri, 21 Dec 2012 09:15:59 GMT

Try this then:

=sum({<Date={"=$(=max({<Date={'<$(=max(Date))'}>} Date))"}>} [sales figure])

Re: Show previous dates data in same row as date selected

Fri, 21 Dec 2012 09:24:17 GMT

Thats not working either - though looks like too many brackets.

I have tried it with all different date formats too.

Ta Jo

Re: Show previous dates data in same row as date selected

Fri, 21 Dec 2012 09:30:23 GMT

I have tried the below to populate the table - Only one date can be selected by the user

//sum({<DateVal={"=$(=max({<DateVal={'<$(=max(DateVal))'}>} DateVal))"}>} SalesRetailGross])

//sum({<DateText={"=$(=max({<DateText={'<$(=max(DateText))'}>} DateText))"}>} SalesRetailGross])

sum({<DateId={"=$(=max({<DateId={'<$(=max(DateId))'}>} DateId))"}>} SalesRetailGross])

DateVal	DateText	Sales2012-09-19	Previous Date
19/09/2012 00:00:00	2012-09-19	£1145448.02	0

Not sure how to attached the QV to this discussion

Many Thanks

Re: Show previous dates data in same row as date selected

Gysbert_Wassenaar — Fri, 21 Dec 2012 10:56:20 GMT

Ok, it's a bit more tricky than I accounted for. Attached is an example that works. I use a variable to calculate the previous date and use the variable in the expression for the previous sales. Also I use a calculated dimension so only the selected date shows in the table. I hope this helps you solve your challenge. In the expression for the variable you need to make sure that the Date field you use is a real date, not a text string. Otherwise the max function doesn't work.

Re: Show previous dates data in same row as date selected

Fri, 21 Dec 2012 15:45:56 GMT

Thankyou very much - I shall try this once im back from my xmas break 🙂

Re: Show previous dates data in same row as date selected

Wed, 02 Jan 2013 09:42:11 GMT

Thanks that works great thankyou :-).