Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
aveeeeeee7en
Valued Contributor III

Logic Required in place of Hard Code

Hi All

I have a Transaction Table which has Sales Amount.

I have another Table called Calendar Table Which shows Calendar Details as per Company defined Dates.

Eg.   For January, it shows - 1 Jan - 27 Jan,

        For February - 28 Jan - 26 Feb,

        For March - 27 Feb - 26 March,

     & For April - 27 March - 28 April, and so on.

I have added this Calendar Table in Transaction Table based on the Month field Key.

From the Calendar Table i am showing Max Month only.

But there is an issue which i am facing:

As per the my Scenario, the Calendar is showing only April Month which has started today only.

In the staring of the Month some Zones had made no Sales or Business.

Eg. Today the Date is 27 March 2014, as per Calendar this will come in April Month.

Suppose There are 4 Zones: North, South, East & West.

Sales made by Zones on 27 March 2014, North - 7 Lacs, South - 5 Lacs, East - O & West - O

For Max Year, i have use variable = Max(Year), &

For Max Month, i have used variable = only(CalendarTableMonth) Since this table has only 1 value so it shows me April.

I have used Set Analysis which give me different Zone Sales by picking Max Month & Max Year

The Sales is showing perfectly for North & South Zones since the Transaction Table & Calendar is joined on the basis of Month field and their April Month is Generated in Transaction Table.

The East & West cases are not appearing in the chart because their sales have not been made in April Month and their April Month is not Generated in Transaction Table so, it is not showing up.

But i want if the Zones does not make sales it should show 0.

I am getting this only when i change my Max Month expression and doing it Hard Coded as 'Apr' than it will give me Perfect Result as:

North: 7 Lacs

South: 5 Lacs

East:   0

West:  0

But, the Problem is that i don't want to do Hard Code. I want the system to do this automatically.

Is there any Workaround for this.....??

Regards

Aviral Nag

1 Reply
jsaradhi
Valued Contributor

Re: Logic Required in place of Hard Code

doesn't an if statement work?

if(/*your expression*/=null(),0,/*your expression*/)

Community Browser