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

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
stekol61
Creator
Creator

Exclude/include dimention a table

Hi!

In my load script i have created two variable:

if ((year(inc_start_date) &'-'& month(inc_start_date)='2019-9'),1,0) as start_check,
if ((year(inc_cleared_date) &'-'& month(inc_cleared_date)='2019-9'),1,0) as clear_check,

In the table I created (see below) these two expression and the result is OK. Only the sum of start and clear without any dimension:

Count({<start_check = {"1"}>}winc_ticket)
Count({<clear_check = {"1"}>}winc_ticket)

In the load script the month is hard-coded but i want to be able to select one or several months and present the result per month in the table.

 

start_clear table.PNG

Below is the data used with more dimensions

 

start_clear table FULL.PNG

7 Replies
Stoyan_Terziev
Partner - Creator III
Partner - Creator III

Hello,

 

Please bear in mind as long as you've hard-coded the month in how you calculate the field, if you use the expression 

Count({<start_check = {"1"}>}winc_ticket)

You will always get (the 0s may get suppressed):

Aug = 0

Sep = 117

Oct = 0

Nov =0

 

I think on your back-end you should create the dimension:

(year(inc_start_date) &'-'& month(inc_start_date) as TicketMonth

 

And then on the front-end create a bar chart with the Dimension [TicketMonth] and the measure 

Count(winc_ticket)

 

Let me know if that's what you are trying to achieve.


Kind regards,

S.T.

y_grynechko
Creator III
Creator III

Hey,

what do you mean by: the month is hard coded? 

To see the stats by month you should be able to create filter pane with the use of this dimension: 

Month(inc_start_date) or Month(inc_cleared_date). 

After picking one or more it should filter out the table. 

stekol61
Creator
Creator
Author

Hi!

I had created in the load script

Year(inc_start_date)&'-'& Month (inc_start_date)as Month,

Year(inc_cleared_date)&'-'& Month (inc_cleared_date)as Month_C

The reason is that start date (Create) and cleared date (Cleared) are calculated differently and this is causing the problems i think

stekol61
Creator
Creator
Author

Hi!

With hard-coded month I mean '2019-9' in the expression:

f ((year(inc_start_date) &'-'& month(inc_start_date)='2019-9'),1,0) as start_check,

The problem is that Month(inc_start_date) and Month(inc_cleared_date) are calculated differently and are not always the same

stekol61
Creator
Creator
Author

Hi!

I updated the calculations:

Year(inc_start_date)&'-'& Month (inc_start_date)as Month,

Year(inc_cleared_date)&'-'& Month (inc_cleared_date)as Month_C,

if ((year(inc_start_date) &'-'& month(inc_start_date)=Year(inc_start_date)&'-'& Month (inc_start_date)),1,0) as start_check,
if ((year(inc_cleared_date) &'-'& month(inc_cleared_date)=Year(inc_cleared_date)&'-'& Month (inc_cleared_date)),1,0) as clear_check,

 

This seems to work if I use two different tables. One for 'Month' and one for 'Month_C'

How can i combine this to one table or graph?

start clear 2.PNG

 

Stoyan_Terziev
Partner - Creator III
Partner - Creator III

Hi,

 

This can get resolved on the back-end.

The below approach will help you combine the Month and Month_C dimensions into a single field but will also keep the start_check and clear_check as separate fields so you can combine them in a single table.

 

FactTable:

Load 

Year(inc_start_date)&'-'& Month (inc_start_date)as Month,

if ((year(inc_start_date) &'-'& month(inc_start_date)=Year(inc_start_date)&'-'& Month (inc_start_date)),1,0) as start_check

FROM YourSource.qvd (qvd)

;



CONCATENATE(FactTable)

Load 

Year(inc_cleared_date)&'-'& Month (inc_cleared_date)as Month,
if ((year(inc_cleared_date) &'-'& month(inc_cleared_date)=Year(inc_cleared_date)&'-'& Month (inc_cleared_date)),1,0) as clear_check

FROM YourSource.qvd (qvd)

;

 

stekol61
Creator
Creator
Author

Hi!

Tried this but the result is not OK in this table.

Calculation used: 'Count({<start_check = {"1"}>}winc_ticket)'

start_check1.PNG

However, this table with more dimensions show the correct result. I can also see that the new 'Month' filter works as it should.

Calculation used: 'if ((year(winc_start_date) &'-'& month(winc_start_date)=Month),1,0)'

start_check2.PNG