Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor II
Contributor II

Problem with Set Analysis that Does Not Ignore a Dimension

Hi.

I have the following problem that I cannot solve despite hundreds of Set Analysis expressions:

set analysis.png

 

Since in my visualization table the yearmonth variable is derived from the manufacturing date, the count of invoices is limited to the number of batches manufactured that month, and not the entire 17 billed that month. I tried

billed_batches = count({$<Manufacturing_Date = >} batchID)

or

billed_batches = count({$<yearmonth = >} batchID)

or

billed_batches = count({$-1<yearmonth={"20*"}>}batchID)

... but no good results 😞

Or should I use the Aggr function:

1st: calculate 

sum(Aggr(count((batchID)),yearmonthbilling))

2nd: calculate

the allocation of this previous out put across yearmonth ... but have no clues about the syntax.

 

Could anyone please help?

Thank you in advance.

Eli

1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

You may try it with something like this:

count({< Batch = {"=month({Manufacturing Date])=month([Billing Date])"}>} Batch)

but often it's needed to create a canonical calendar to link all related date-fields within an extra calendar. More background could you find here: Canonical Date

- Marcus

View solution in original post

2 Replies
Highlighted
MVP & Luminary
MVP & Luminary

You may try it with something like this:

count({< Batch = {"=month({Manufacturing Date])=month([Billing Date])"}>} Batch)

but often it's needed to create a canonical calendar to link all related date-fields within an extra calendar. More background could you find here: Canonical Date

- Marcus

View solution in original post

Highlighted
Contributor II
Contributor II

Thank you so much for helping me fixing this issue.

The Canonical Calendar was the solution, and henric_cronstrom's post was of great help

Awesome !!!