Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
amaaiia
Contributor
Contributor

Pivot table with external column field (dimensional date table with no common fields with fact table)

Hi!

I have the following data:

IDTypemanufacturing_monthsale_month
1T1Nov/2019Feb/2020
2T1Jan/2020Mar/2020
3T2Jan/2020Jun/2020
4T3Sep/2019Jun/2020
5T2Sep/2019Dec/2019
6T3Oct/2019Jun/2020

 And I want to create a Pivot Table like this:

Stock_month (right) -->

Type(below)

Jul/2019Ago/2019Sep/2019Oct/2019Nov/2019Dec/2019Jan/2020Feb/2020Mar/2020Apr/2020May/2020Jun/2020
Total002343543330
T1000011210000
T2001110111110
T3001222222220

It is a stock table, that is, for each stock_month (months that I select through filter pane) I want to check how many  IDs have been manufactured at this stock_month or before (manufacturing_month <= stock_month), and have not been sold yet (sale_month >= stock_month).

I can't select manufacturing_month or sale_month as column for the pivot table because not all dates appear. So I have manually generated a new table with months from Jul/2019 to Jun/2020 with column name stock_month. I have added new filter pane with 'Stock' alternate state. The pivot table has Type as dimension, and aggr(only({Stock}stock_month),stock_month) as column. The measure is th following:

sum(aggr(if([sale_month]>= max({Stock}stock_month) and manufacturing_month<=max({Stock}stock_month),1,0), ID,stock_month))

It does what I want but only when I manually select a short amount of IDs, because with all the data loaded it gets stucked and the pivot table does not work (aggr function is not the best way for such amount of data).

I've tried with set analysis:

count({<ID={"manufacturing_month <= max({Stock} stock_month)"}+{"sale_month >= max({Stock} stock_month)"}>} ID)

but when max({Stock} stock_month) is inside set analysis it does not get columns stock_month, but the TOTAL stock_month (the maximum selected in the filter pane).

 

Labels (1)
1 Solution

Accepted Solutions
sunny_talwar

I suggest using Interval Match to get this to work. Stock_Month will be interval matched to Manufacturing_month (Start of stock_month) and sale_month (End of stock_month).

View solution in original post

3 Replies
sunny_talwar

I suggest using Interval Match to get this to work. Stock_Month will be interval matched to Manufacturing_month (Start of stock_month) and sale_month (End of stock_month).

NadiaB
Support
Support

Hi @amaaiia 

The description is a little confusing, but it seems there is no master calendar. when it comes to the use of Island tables in general is not recommended.

Hope it helps.

Note: if this suggestion helps to resolve the issue or answers your question, it will be appreciated if you select the "Accept as Solution" option.
We encourage you to visit our Knowledge Base https://qliksupport.force.com/QS_CoveoSearch when trying to find a solution related and not limited to: errors, feature requests, product defects. You can also visit our Qlik Help portal https://help.qlik.com/
amaaiia
Contributor
Contributor
Author

It's a good idea but I have a lot of data.  If manufacturing_month is Jan/2000 and sale_month is Dec/2010 (this can be possible), there would be lot of duplicated rows... but nice approach