topic Re: Combining Year/Month Fields in QlikView

Combining Year/Month Fields

khaycock — Wed, 25 Nov 2020 16:16:04 GMT

I have a date field that we want to use the month and year parts of to get a summary of data for those dates. This has what been put in the script...

Date([Doc. Date],'YYYY-MM') as DOCDATE,

The table is fine when a year is selected, but when the user selects a specific month, I would want the data to be summed into one column than split like this

As you can see, we have 2 different columns for 2017-05 and I would only expect to see 1.

Can we do this?

Re: Combining Year/Month Fields

prieper — Tue, 04 Jul 2017 13:43:57 GMT

You have two different dates behind Doc.Date, the formatting shows them only as if it is the same.

Try to use

DATE(MONTHSTART([Doc.Date], 'YYYY-MM') AS DOCDATE

Thus all dates will have the same value and will be displayed in the required format.

Peter

Re: Combining Year/Month Fields

khaycock — Tue, 04 Jul 2017 14:12:02 GMT

The table is now saying there is no data to display

Re: Combining Year/Month Fields

Anonymous — Tue, 04 Jul 2017 14:15:38 GMT

Try this

Monthname(Monthstart([Doc.Date])) As DocDate

Regards

Mahesh.

Re: Combining Year/Month Fields

prieper — Tue, 04 Jul 2017 14:31:40 GMT

yes, a ")" was missing

Re: Combining Year/Month Fields

jonnaamb — Tue, 04 Jul 2017 14:39:09 GMT

Hi Kathryn,

Why can't we model the data in such a way that you have Month and Year as two different dimensions (by using SubField function) along with existing Doc date in the same table so, during the selection you can simply use a List box for Month and another list box for Year and select based on which you can see the bar chart. I'm not sure if the simplification helps you meet the requirement.

Best regards,

Ambareesh J

Re: Combining Year/Month Fields

khaycock — Tue, 04 Jul 2017 14:41:09 GMT

Hi Ambareesh,

We also had a master calendar connected to a different date within this data set, this one needed to be seperate

Kathryn