2 Replies Latest reply: May 13, 2017 9:57 AM by Dr.Maks Clay

Problem with IF construction!!!

Hi, Guys!!!

Need Your Help with following problem!

Describing my situation!!!

I need to show in diogram (any) the information about the products wich were sold in LastMonth better than in PreviousMonth!
For example:
Product    Jan    Feb   March    Apr    May   June

Opel          3        5         1        13      13       11

Mazda       12      11        5        17      16       21

Nissan       31      15       21       33      43       55

Audi          31       51       23       22      7        19

As you can see The LastMonth here is June. Mazda, Nissan, Audi are the products wich were sold better in LastMonth than in PreviosMonth. Because of Opel was sold worse in LastMonth, I should't show it in my final diogram!

I want to get this type of diogram.

Product    Jan    Feb   March    Apr    May   June

Mazda       12      11        5        17      16       21

Nissan       31      15       21       33      43       55

Audi          31       51       23       22      7        19

I have 2 variables. MaxMonthVar and PreviosMonthVar! I thought, If I'll use expression "IF-ELSE" construction, can I get what I want?
MaxMonthVar=MONTH(MAX([SaleDate])).

Something like This:
IF (  ALT(SUM({<Month={'\$(MaxMonthVar)'}>}Quantity),0)>ALT(SUM({<Month={'\$(PreviousMonthVar)'}>}Quantity),0)  ) THAN "SHOW THE RESULT WICH I NEED" ELSE (...)

• Re: Problem with IF construction!!!

SUM({<Month={'\$(MaxMonthVar)'}>}Quantity)

resp.

SUM({<Month={'\$(PreviousMonthVar)'}>}Quantity)

do already work, since Qlik might be a bit pickywith the set expression syntax. If they don't, see also

Dates in Set Analysis

The Magic of Set Analysis - Point In Time Reporting • Blog • AfterSync

Then you can filter your Products (that's what you basically want to do) using set analysis with an advanced search / expression search:

=Sum(

{<Product = {"=SUM({<Month={'\$(MaxMonthVar)'}>}Quantity) >SUM({<Month={'\$(PreviousMonthVar)'}>}Quantity) "} >}

Quantity)

Using this expression in a chart (e.g. straight table or pivot table) with dimensions Product and Month should return what you requested.