Date/Calendar integration

[peschu123]

Hi,

I have a problem which drives me crazy.

I have several datefields in several tables in my qvw file ^^.  I took a look at the master calender etc. (which works fine).. But every solution I try leads to a much bigger problem. For all Dates I create the Fiscal Dates like FY12/13FP05 (Financial Year 12/13 Financial Period 05). Everything works fine in charts etc. if they are isolated. The Problem starts in the Dashboard...

Ok what I want to do: I want to create a central Menu in the Dashboard, where you can choose the Year and Period, like you can see in many dashboards. And in the Charts, QV should use the corresponding Date for the dimension.

Example: If I choose FY13/14FP02(actual period), QV should show me in a chart all Production Orders with Delivery Date at this date. In another Chart it should show me all production orders which are finished at this time. Sounds not like a big problem...

Perhaps I think to complicated, but I need some kind of meta calendar which is above the 2 dimensions. But when I create it for example in a style of a link table, I run into circular references, synthetic keys etc.(synthetic keys are not a real problem...). But when I try to solve the circular reference problem, my dashboard shows really weird values. Also tried a solution with an isolated calendar table and some IF statements in the expressions. This solution works, but it is complicated and it leads to very heavy performance issues(I think because the statement is not just inside one table). By the way, the same expression as Set Expression didn't work!? ...

I would appreciate any help with this problem.

[Gysbert_Wassenaar]

Should be possible with set analysis expressions if you select only one period. Make a selections in one field and use it also for the other field.

chart1: count({<DeliveryDate={$(=only(DeliveryDate))}>} Orders)

chart2: count({<DeliveryDate=, FinishedDate={$(=only(DeliveryDate))}>} Orders)

If you need more help, please prepare an example document.

[peschu123]

Hi Gysbert,

thank you for the quick reply.

If I understand your expression right, I have to choose one Date Field for the Dashboard and then use the upper expression to pick the corresponding dates from the other date field?

I tried it but I get "No Data to Display" 😕

My expressions look like this:

sum({<Del_Relevant_FYFP=,CAPACITY.capacity_FYFP={$(=only(Del_Relevant_FYFP))}>}CAPACITY.EXT_RES_TAG.IST_KAP) / 60

The date field used in the dashboard is Del_Relevant_FYFP

The other chart works perfect with Del_Relevant_FYFP as Dimension and some aggregation expressions.

e.g. Sum({$<ontime={1},OTD_Relevant={1}>}OTD_Counter)

They are in different tables.

I can imagine this is hard to understand. If I can't solve this, I will try create a sample file...

Thx anyway for your help.

[er_mohit]

Try this

sum({<Del_Relevant_FYFP={"=$(=only(Del_Relevant_FYFP))"},CAPACITY.capacity_FYFP=>}CAPACITY.EXT_RES_TAG.IST_KAP) / 60

[peschu123]

I created an example file and tried to keep the structure as simple as possible. I think my problem is visible with this example.

I hope there is a simple solution, perhaps it is fail by design...I don't know why this is such a problem for me perhaps a little "burnout".

I appreciate any help, thank you in advance!!

[hic]

If you apply this demand on the data model, it will lead to a theoretical inconsistency. A generic date that means delivery date sometimes and finished date otherwise, cannot be used. But there are some work-arounds:

• Set analysis (tied to a specific expression) just like Gysbert and others suggest.
• A generic date in a link table that picks out orders where any of the date fields match the selection in the generic date. (See e.g. http://community.qlik.com/message/283501 )
• A generic date in a logical island and expressions in the charts using "if(GenericDate=DeliveryDate,..." or Set analysis.

HIC

[peschu123]

Hi Henric,

thank you for your reply.I really appreciate your help. This would have helped me a lot on friday. I think I found a solution by myself, like you mention as no. 2 of your 3 choices. The solution described by Rob Wunderlich.

I'm aware of the fact with this "inconsistency" you mention. But I think it can be very well controlled in the charts. It's not a typical Order/Sales/Region application. The users just need to know which period they want to look at and in the production environment there is always just the actual day or period. And with individual calendars I think the data can be further reduced if it is neccessary at some point.

Now, I thought a quite long time about this topic and I'm pretty sure in my case it would be more confusing if there are different Date selections.

And it was a good training on data modelling. ^^ I hope it will work like expected in my main QVW.