Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Highlighted
maahivee
New Contributor III

Displaying Month From Different Dates in one Report

HI Guys - I have a very strange requirement and I am having a bit of trouble figuring this one.

I need to display month of different dates from different tables in one single report as a dimension. it is not possible to combine these dates in the back end as they are from different tables in different modules and doesn't have any relation between them.

The months needs to extracted and displayed from each of these dates for each of these rows.

   

       JanFebMarAprMayJunQ2JulAugSepQ3OctNovDecDecQ4Total Year
2018 Sales Orders by Date11212121212121212121212121212121212
2018 Purchase Orders by Date213451345134513451345134513451345134513451345134513451345134513451345
2018 Forecast by Date314561456145614561456145614561456145614561456145614561456145614561456

I guess this table should give you an idea of what i am trying to do, first row values should be displayed by using month from Date1,

second row values should be displayed by using month from Date 2 and so on, the difficult task here is combining and displaying all these into a single report so when the user downloads it, it goes in one excel. these all are different dates from different tables in different modules, so there is no way of joining these, but just for this one report for comparison purposes i need to display like this.

How to create monthly buckets with different dates in one single report is what my question is.

6 Replies
qvraj123
Contributor II

Re: Displaying Month From Different Dates in one Report

is there any way you can combine the data in Qlikview - since this cannot be combined in the backend

something like

LOAD

FIELD1,

FIELD2,

FIELD3,

'SOURCE1' AS DATA SOURCE

FROM SOURCE1

CONCATENATE

LOAD

FIELD1,

FIELD2,

FIELD3,

'SOURCE2' AS DATA SOURCE

maahivee
New Contributor III

Re: Displaying Month From Different Dates in one Report

Hi Raj - I am sorry when i said backend i meant QV backend, no i cannot combine data like that because they are all different modules different data we are talking about here. 

qvraj123
Contributor II

Re: Displaying Month From Different Dates in one Report

got it - but based on what you provided it might be hard for someone to help - may be you scramble data

and provide some sample data with those 2 sources -

maahivee
New Contributor III

Re: Displaying Month From Different Dates in one Report

Hi Raj - Please find attached. This has multiple sheets Sales orders, Purchase Orders, Forecast  sheets are the raw data in the system.

'Final' sheet has what the expected result is supposed to look like. I created 3 different tables using raw data just to show as a step what i am trying to achieve but next to it is the final result that i am looking for.

So in the final sheet you can see what i am trying to achieve is the combined month field using those 3 month fields, but should be done only on the front end report because this data does not relate to each  other in the backend. 

qvraj123
Contributor II

Re: Displaying Month From Different Dates in one Report

I think combining the datasets is what you can try within the Qlikview

Do you have these 3 different QVD files or do you have the data in 3 different excel files

DateField                   AS Month,

'Sales'                        AS Data_Source,

Sales_Amount           AS Sales_Amount

From Your_Sales_QVD_or_ExcelFile

Concatenate

DateField                  AS Month,

'Purchase'                 AS Data_Source,

Purchase_Amt         As Purchase_Amount

From Your_Purchase_QVD_or_ExcelFile

Concatenate

DateField                    As Month,

'Forecast'                    AS Data_Source,

Forecast_Amt             AS Forecast_Amouunt

From Your_ForecastQVD_Or_ExcelFIle

Partner
Partner

Re: Displaying Month From Different Dates in one Report

Hi,

Please find attached the Solution Qvw, i have used the Date bridge in-order to combine the different dates and also used inline table to create the dimension fields values as Forecast value, purchase order, sales order.

Solutions.PNG

If you find this helpful please mark as helpful or correct.

Regards,

Bhasker Kumar