topic Re: Issue with sales and Target values in QlikView

Issue with sales and Target values

Wed, 07 Sep 2016 11:09:39 GMT

Hi All,

I have two tables in my data model, 1 which contains the Sales Information and the other with Target information.

in Sales table the data will be on daily basis whereas in target file the target is on Monthly basis.

Apart from that i have Product info also in my target file.

I have create a straight table with Product as Dimension and Actual and Target as Expressions,without selection it would display latest month data and based on selection of month and year it should change accordingly.

I have made a composite key with Product and Month and have linked the two tables Sales and Target. The value are coming as expected though i have one issue.

whenever there is no "Product" in Actual table the target will not be visible as i have made a Key using both product and Month.

I have to display the Target even if there is no Actual Sales.

How can i achieve the same?

Thanks in advance!

Best Regards,

Reena

Re: Issue with sales and Target values

dmac1971 — Wed, 07 Sep 2016 11:12:28 GMT

Reena,

Do you have a master calendar covering both your sales and budget numbers? If so you can then group all sales, by dealer, region etc against target/budget, using for example Month-Year as your dimension?

I have a number of reports like this, they work really well.

Dermot.

Re: Issue with sales and Target values

Thu, 08 Sep 2016 04:08:37 GMT

Hi Dermot,

I have a master calendar to which Actual table is linked based on Date.

If possible can you please share an example .My dimension in straight table would be the product and expressions..sales and target.

Best Regards,

Reena

Re: Issue with sales and Target values

Thu, 08 Sep 2016 04:39:59 GMT

Hi Reena,

You can go for concatenation.

Concat 2 tables based common fields it will concatenate.

Create one custom field say 'TransFlag' in both table

I believe fields for both the tables will be,

Sales :

Target ;

In expression you can put

For sales : {<TransFlag={'Actual']>}

For Target: {<TransFlag={'Target']>}

Thanks,

Nitin

Re: Issue with sales and Target values

kaushiknsolanki — Thu, 08 Sep 2016 06:48:27 GMT

Hi,

You should use the Link Table or the concatenate option.

What I feel because the granularity of both table is not same, you should use the link table option to get the data from Target table which is currently missing.

Regards,

Kaushik Solanki

Re: Issue with sales and Target values

dmac1971 — Thu, 08 Sep 2016 08:31:00 GMT

Ok So load your sales data and target data in the same table using something like :

Where Month(DATE) <= Month(Today()); or say where less than or equal to max(SalesDate) etc.

My master calendar is as follows :

TempSalesOrderMinMax:
Load min(SalesDate) as MinDate,
max(KPIDateID) as MaxDate
Resident [SALES_DATA]
;

Let vStartDate= Peek('MinDate');
Let vEndDate=Peek('MaxDate');
Let vNumDays=vEndDate-vStartDate+1;

drop table TempSalesOrderMinMax;

SalesCalendar:
Load
TempDate as KPIDateID,
Date(TempDate) As Date,
Year(TempDate) As Year,
Month(TempDate) & '-' & Year(TempDate) As MONTH_YEAR,
Month(TempDate) As Month,
Num(Month(TempDate)) As Month#,
'Q' & Ceil(Month(TempDate)/3) as Quarter,
'Q' & Ceil(Month(TempDate)/3) & '-' & Year(TempDate) As Q_YEAR,
NetWorkDays(TempDate,TempDate) as NetworkDay_Flag,
Day(TempDate) As Day,
Week(TempDate) As Week,
WeekDay(TempDate) As WeekDay,
if(((Year(TempDate) <= Year($(vEndDate))-1) And (Month(TempDate) <= Month($(vEndDate))) Or Year(TempDate) = Year($(vEndDate))),'YTD','ROY') as YTD_Flag
// if((Date(TempDate) >= Yearstart(Today()) AND Date(TempDate) <= Today()) OR (Date(TempDate) >= YearStart(AddMonths(Today(),-12)) AND Date(TempDate) <= addmonths(Today(),-12)),'YTD','ROY') as YTD_Flag
// -YearToDate(TempDate, 0, 1) As YTD_Flag,
// -YearToDate(TempDate, -1, 1) As LYTD_Flag

;
Load
$(vStartDate)+RecNo()-1 As TempDate
AutoGenerate($(vNumDays));

Re: Issue with sales and Target values

Tue, 27 Sep 2016 17:19:24 GMT

Hi Reena,

Maybe you can get some inspiration from a video with a similar problem, detailed data vs. aggregated data.

Power of Qlik Script - Reshaping Data Part 2. I know that the video is for Qlik Sense, but the scripting is the same for QlikView. It is colse to the end of the video where it shows how to handle the aggregated data in combination with the detailed data.

Power of Qlik Script - Reshaping Data - Part 2 (video)

Finn