# New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Contributor

## Set Analysis Date Variable

All,

I am using a varible to select a date, a point in time. I have created a chart that calculates the LAST transaction BEFORE that date for each Company / Product Combination. This is working as it should (in the example it is showing the last date being the 2nd or the 3rd

=Max({<IndexDate = {'<\$(=Date(vDateFrom))'}>}IndexDate)

I then want to look at the sum of the sales that happened on that date, and this is where I'm having the issue

=Sum({<IndexDate={'\$(=Date(Max({<IndexDate = {'<\$(=Date(vDateFrom))'}>}IndexDate)))'}>}Sales)

It is only doing the calculation at a global, not at the company / product level as above (i.e. only doing the calculation for the sales on the 3rd

Please see attached for details. I dont know if I need to reference the previous column (I understand this cant be done) or do some sort of aggr function, but nothing I've tried works and I dont understand why this one doesn't

I want it to show

Company               Product               Last Trans Date               Sales on that Date

ABC Ltd                 Product 1            03/01/2017                        9000

ABC Ltd                 Product 2            02/01/2017                        1000

XYZ Ltd                 Product 1            02/01/2017                        7000

XYZ Ltd                 Product 2            03/01/2017                        13000

Thanks,

Karl

Tags (4)
1 Solution

Accepted Solutions
Highlighted
MVP

## Re: Set Analysis Date Variable

May be use something like below

Expression

1)

Max({<IndexDate = {'<\$(=Date(vDateFrom))'}>}IndexDate)

2)

FirstSortedValue({<IndexDate = {'<\$(=Date(vDateFrom))'}>}Aggr(SUM({<IndexDate = {'<\$(=Date(vDateFrom))'}>}Sales),IndexDate,Company,Product),-Aggr(IndexDate,IndexDate,Company,Product))

14 Replies
Highlighted
Esteemed Contributor III

## Re: Set Analysis Date Variable

what is the variable values?

Highlighted
Contributor

## Re: Set Analysis Date Variable

The idea is that you can enter any date you like and can look at the most recent transaction before that date. In my example I've set vDateFrom to be 05/01/2017

Highlighted
Esteemed Contributor

## Re: Set Analysis Date Variable

Hi,

Please find attached the app with the correct formula

Contributor

## Re: Set Analysis Date Variable

Youssef,

Thank you very much, but that shows me the sum of all transactions lbefore the selected date, I need to only see the sum of the transactions on the last date before the selected date. This will be different for each Company / Product combination

Highlighted
Esteemed Contributor

## Re: Set Analysis Date Variable

Thats what I see on the table.. what is the wrong value ?

Highlighted
Contributor III

## Re: Set Analysis Date Variable

You can use this expression  for sales:

max({<IndexDate = {'<\$(=Date(vDateFrom))'} >}Sales)

Highlighted
Contributor

## Re: Set Analysis Date Variable

For example,

Company ABC Ltd and Product 1. The last date was 03/01/2017. The sales on that day was 9000, your solution picks up that and the 5000 from 01/01/2017 and so shows 14000

Thanks

Highlighted
MVP

## Re: Set Analysis Date Variable

May be use something like below

Expression

1)

Max({<IndexDate = {'<\$(=Date(vDateFrom))'}>}IndexDate)

2)

FirstSortedValue({<IndexDate = {'<\$(=Date(vDateFrom))'}>}Aggr(SUM({<IndexDate = {'<\$(=Date(vDateFrom))'}>}Sales),IndexDate,Company,Product),-Aggr(IndexDate,IndexDate,Company,Product))

Highlighted
Esteemed Contributor

## Re: Set Analysis Date Variable

Sorry.. you can use praveen solution below.