topic Re: Help needed in QlikView

Help needed

ali_hijazi — Tue, 22 Nov 2016 12:05:52 GMT

Hello

I have a pivot table and the user selects a month and a year

in the pivot table I want to display the for each customer that value of the invoices whose date is the maximum before the selected month

i.e. if the user selects 2016,OCT then I want to calculate the sum of the Sales amount of the invoices whose date is before OCT 2016

customers may have invoices last invoiced on Apr 2015 others may have the last invoice dated on Sep 2016 and so on

Please advise

Re: Help needed

hemanthaanichet — Tue, 22 Nov 2016 12:33:32 GMT

Can you be able to share some raw data with the expected output to help you better? It will be pure guesswork without a sample as its difficult to understand what you are trying to get

Regards

Hemanth

FirstSortedValue

Re: Help needed

hani_saghir — Thu, 24 Nov 2016 08:07:38 GMT

Pardon my ignorance, but should the below simply work?

=sum(if([Transaction Date] < MakeDate(vYear, vMonth,1) ,Value, 0))

with vMonth and vYear the selected month and year

Re: Help needed

ali_hijazi — Thu, 24 Nov 2016 10:19:55 GMT

No not this way

what I want is calculate the amount of the invoices whose date is the maximum date for each customer and is just before the selected date