Syntax for The amount of change in Net Sales value from previous year to current year by Customer?

[Anonymous]

Hello Everyone,

I need a few formulas to calculate :

1. Net Sales Change: The amount of change in Net Sales value from previous year to current year by Customer.

2. Budget – Actual Revenue: The comparison of budget revenue amount to actual revenue amount by year.

3. Possible future enhancements: Market Share

I have Order_Date field, Sales is calculated in variable $(vNetSales). I'm trying Set Analysis but the result showing the error saying Nested Aggregation is not possible.

Please let me know.

Regards,

Pradnya

[Gysbert_Wassenaar]

Please post a small qlikview document with example data.

[Anonymous · Accepted solution]

Hello All,

OrderDate is date field, Sales field

Year(OrderDate) as OrderYear

vMax = Max(OrderYear)

vPrev = Max(Orderyear)-1

If I apply following formula, QV evaluates correct result

=sum({$< OrderYear = {2004}>} Sales) -  sum({$< OrderYear = {2003}>}  Sales)

But, if I apply below formula, QV gives an error saying "Error in set modifier ad hoc element is: ',' or ')' is expected"\

=sum({$< OrderYear = {$(vMax)}>} Sales) -  sum({$< OrderYear = {$(vPrev)}>}  Sales)

I got the rest of my questions, I'm just stuck into this silly easy thing.

[Gysbert_Wassenaar]

Make sure the expressions in your variables start with an = symbol.

[sunny_talwar]

Try this:

=Sum({$<OrderYear = {$(=$(vMax))}>} Sales) -  Sum({$<OrderYear = {$(=$(vPrev))}>} Sales)

or this:

=Sum({$< OrderYear = {$(=vMax)}>} Sales) -  Sum({$<OrderYear = {$(=vPrev)}>} Sales)

[Anonymous]

Yes I get the Solution. gwassenaar‌ can u tell me why "=" sign is used?

[Gysbert_Wassenaar]

The = sign is used so that the expressions in the variables are evaluated outside the context of the chart. In other words the min and max are not calculated per row but only once at the document level.