Issue with sales and Target values

[#1]

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

[dmac1971]

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.

[#3]

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

[#4]

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

[kaushiknsolanki]

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

[dmac1971]

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));

[#7]

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)

BR

Finn