Expression while filtering displays correct value but not when clears filter.

[#1]

Dear QV Experts,

In the expression, need to display PriorYTD.

Earlier it was taken as 12/31/2014 for all the distributors.

Now we modified the query & MaxSalesDate for each & every distributor will be stored in a variable.

For some distributors, the MaxSalesDate is 01/31/2014, so we need to display the sales only till that date.

I have an issue, when i filter selecting the distributor, it is displaying correctly.

But when i clear filter, if it shows all the customers, then that variable is not populated & the value for the distributors is showing the whole year sales value.

THe screenshot,

The first row shows by filtering the Distributor. [$2,197,811]

The second row which shows after clearing the filter. [$26,925,581]

The expression which is used below

PYMaxCustDate is different for different distributors. That is why it is not impacting if i clear the filter.

Can you please suggest me on this.

[Gysbert_Wassenaar]

The set is calculated at the chart level, not the row level. So if different distributors have different PYMaxCustDate values then you need to use an if statement instead of set analysis.

[sathishkumar_go]

I would suggest you to create a flag in your data modelling based on the max date of the each distributor.

Example:

Disrtributor Date Flag

Disti A   01-01-2016 0

Disti A   02-01-2016 1

DISTI B 01-01-2016 0

DISTI B 02-01-2016 0

DISTI B 03-01-2016 1

After that pass flag value in the expression.

-Sathish

[#4]

Thanks Gysbert. Can you able to provide some workaround or samples.

[#5 · Accepted solution]

Thanks Gysbert & Satish.

Used If condition in the expression & it is working as expected.

SUM(IF(Date >= Date(PYStartDate) and Date<= Date(PYMaxCustDate), CustomerSalesPrior))