Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Toronto Sept 9th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
er_mohit
Master II
Master II

If and Else Logic on backend

Hi ,

I have a data like below.

Year:

LOAD

* Inline [

Month,Year

1,2014

2,2014

3,2014

4,2014

5,2014

6,2014

7,2014

8,2014

1,2013

2,2013

3,2013

4,2013

5,2013

6,2013

7,2013

8,2013

9,2013

10,2013

11,2013

12,2013

];

If User select Year = 2013

i want an if condition logic that

In above i have 1 to 12 minth for Year = 2013 and for Year = 2014 Month is 1 to 8

I want to create a dimension based on MaxMonth group by year.

Compare that MaxMonth with given Month group by Year.

So, what i want the logic made like below

So, my logic is if Month = 1,'Jan',

Month<=2,'Jan & Feb',

Month>=1 and Month <=3,'Q1',

Month<=4  ,'Apr',

Month>=4 and Month<=5,'Apr & May',

Month>=1 and Month<=6,H1,

Month=7,July,

Month>=7 and Month<=8 ,'July & Aug',

Month> = 7 and Month<=9,'Q3',

Month=10,'Oct',

Month>=10 and Month<=11,'Oct and Nov',

Month>=10 and Month<=12,'Q4',

Month>6 and Month<=12,'H2',

Month>=1 and Month<=12,'YTD Dec' as NewDimension 

here the static value m write is MaxMonth of that particular Year.

My Output:

So, for Year =2013

The Value having in NewDimension  field is

Q1

Q2

H1

Q3

Q4

H2

YTD Dec

if select Year = 2014

then in NewDimension field having value alike

Q1

Q2

H1

July & Aug

YTD Aug

Any help will be appreciable.

12 Replies
anbu1984
Master III
Master III

Year:

LOAD

* Inline [

Month,Year

1,2014

2,2014

3,2014

4,2014

5,2014

6,2014

7,2014

8,2014

1,2013

2,2013

3,2013

4,2013

5,2013

6,2013

7,2013

8,2013

9,2013

10,2013

11,2013

12,2013

];

NoConcatenate

MaxMonth:

Load Year,SubField(NewDim,',',IterNo()) While IterNo() <= SubStringCount(NewDim,',')+1;

Load Year, If(MthSum=12,'Q1,Q2,H1,Q3,Q4,H2,YTD Dec',If(MthSum=11,'Q1,Q2,H1,Q3,Oct and Nov,YTD Nov',If(MthSum=8,'Q1,Q2,H1,July & Aug,YTD Aug'))) As NewDim;

//EDIT

Load Sum(Month) As MthSum, Year Resident Year Group by Year;

er_mohit
Master II
Master II
Author

Hi anbu1984 ,

Thanks for you reply

I m checking your output but it doesn't link with Month values. like for Year = 2014

if m select

Q1 then it 'll link to Month field values 1 to 3

Q2 then it 'll link to Month field values 4 to 6

H1 then it 'll link to Month field values 1 to 6

July & Aug then it 'll link to Month field values 7 to 8

YTD Aug then it 'll link to Month foeld values 1 to 8.


Hope you understand. and same relevant for Year = 2014

Here above defined logic have mistake for Apr the line is Month=4  ,'Apr'.

Regards

Mohit

anbu1984
Master III
Master III

Create another table like below and link this table to newly created dimension

Load * Inline [

NewDim,Month

Q1,1

Q1,2

Q1,3

Q2,4

..

Jul & Aug,7

Jul & Aug,8

...

YTD Jan,1

YTD Feb,1

YTD Feb,2

...];

er_mohit
Master II
Master II
Author

Thanks for reply back.

If my Year table also having the Date field then ?

Regards

Mohit

anbu1984
Master III
Master III

Not getting your question

er_mohit
Master II
Master II
Author

Hi

The same logic i applied using master calendar but can't get success.

Please find attachment.

Regards

Mohit

er_mohit
Master II
Master II
Author

Please help me any suggestion

anbu1984
Master III
Master III

MaxMonth table is empty. Can you reload and check.

er_mohit
Master II
Master II
Author

Hi

Please find attachment, Here the issue is MthSum is consolidate all value instead of separately as alike your above example. !

Regards

Mohit