topic Re: Set analysis on monthstart, possible - syntax? in QlikView

Set analysis on monthstart, possible - syntax?

Wed, 25 Nov 2020 16:16:04 GMT

I need some help to understand if what I try to achieve is possible or not and your help is much obliged.

I have to date fields table1.dateA and table2.dateB and one field to count (in the fact table)

COUNT({<monthstart(dateA)= {(=MonthStart(dateB))} >} DISTINCT field_to_count)

Is it possible to only DISTINCT COUNT where MonthStart(table1.dateA) = MonthStart(table2.dateB)?

If yes would you be so kind and advice on the syntax?

Thank you

Re: Set analysis on monthstart, possible - syntax?

sunny_talwar — Thu, 16 Feb 2017 13:00:27 GMT

May be this:

Count({<field_to_count = {"=monthstart(dateA) = MonthStart(dateB)"}>} DISTINCT field_to_count)

Re: Set analysis on monthstart, possible - syntax?

Thu, 16 Feb 2017 14:28:19 GMT

Thank you for the suggestion but this did not work.. I'm stil trying..

Re: Set analysis on monthstart, possible - syntax?

sunny_talwar — Thu, 16 Feb 2017 14:29:06 GMT

Would you be able to share a sample where this isn't working?

Re: Set analysis on monthstart, possible - syntax?

vishsaggi — Thu, 16 Feb 2017 14:35:52 GMT

What are your date formats for your dateA and dateB?

Re: Set analysis on monthstart, possible - syntax?

Thu, 16 Feb 2017 15:14:40 GMT

My dateA format is 2015-10-01 00:00:00

I try to simplify to see if this is possible but Im not able to make this work either:

count({$<dateA={date#('2015-10-01','YYYY-MM-DD')}>} DISTINCT field to count)

I try to filter out only the rows where dateA = 01OCT2015 using a set expression.

Maybe Im trying to do something that cant be done?

I came across this:

Limitation: take care, a set analysis is calculated once per chart (or table). NOT once per row. If you

want a different result in your set analysis according to the row being calculated and displayed, it will NOT

work. Change the way to compute your data (model, if statement) but forget the set analysis.

Re: Set analysis on monthstart, possible - syntax?

vishsaggi — Thu, 16 Feb 2017 15:21:51 GMT

Is your Field to count is actual field name here? Try this? Using sunny's expression

count({$< fieldtocount ={ "= MonthStart(Date(date#(dateA,'YYYY-MM-DD hh:mm:ss') 'YYYY-MM-DD')) = MonthStart(Date(date#(dateB,'YYYY-MM-DD hh:mm:ss') 'YYYY-MM-DD')) " }>} DISTINCT field to count)