topic Re: Problem with IF construction!!! in QlikView

Problem with IF construction!!!

Sat, 13 May 2017 13:08:20 GMT

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])).

PreviousMonthVar==MONTH(ADDMONTHS(MAX([SaleDate]),-1))

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!!!

swuehl — Sat, 13 May 2017 13:46:52 GMT

I assume that your expressions

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.

Re: Problem with IF construction!!!

Sat, 13 May 2017 13:57:49 GMT

Excellent, Stefan! Your method was helpful for me! Thanks a LOT!