Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I am trying to do a pivot chart in Qlikview to show the monthly actual cost vs budget cost of expenses. I had encountered a double count problem, the chart in Qlikview seems to have double count on the expenses. Can anyone advise me, what may have gone wrong or how should I solve this double count issue. Below is the sample of the files I had uploaded and the output I had gotten from Qlikview.
Hi Andy,
PFA qvw file.
Of course better to create unique ID and use only one exists()-expression, but still
In this example you load everything from 1 Sheet and only rows which are not exists (defined by field combination) from 2. To change order just move where condition to another sheet, but note that exists should be used in the second LOAD statement.
Hope this helps.
Andrei
Can You upload you qvw file . it will help us understand better.
Also upload the sample data...
Hi Nitin,
I am unable to upload my excel file into qlikview. Please see my script below:
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='$#,##0.00;($#,##0.00)';
SET TimeFormat='h:mm:ss TT';
SET DateFormat ='DD/MM/YYYY';
SET TimestampFormat ='DD/MM/YYYY h:mm:ss[.fff] TT';
SET MonthNames ='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames ='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
set vMaxMonthYear = =Date(max([Month Year]), 'MMM-YYYY');
set vPriorMonthYear = =Date(addmonths(max([Month Year]), -1), 'MMM-YYYY');
let vMaxDate = '=max(Date)';
let vMaxMonth = '=month(max(Date))';
let vMaxYear = '=max(Year)';
LOAD
FAB_Year as YEAR,
FAB_Month as MONTH,
FAB_DeptM,
FAB_Dept as DEPARTMENT,
FAB_L10,
FAB_L12,
FAB_L14,
FAB_Description,
Forecast_Amt,
Actual_Amt,
Budget_Amt,
Date,
Month(Date) as Month,
Date(MonthStart(Date), 'MMM-YYYY') as [Month Year],
Year(Date) as Year
FROM
(
Theres doesn't seem any error ....
Actually if I will see the application then I can help you in much better way.
Regards
Nitin
I see. No worries. Thanks Nitin, really appreciate your help.
Best regards,
Andy
Hi,
If you are using Straight table then display total row as Computer Cost, rename Total to Computer Cost in
Straight Table Properties -> Presentation -> Select Totals on First Row -> Select Use Label and give Computer Cost.
Hope this helps you.
Regards,
jagan.
Hi Jagan,
Thanks for your reply, I had tried using your method, but I am still getting double count.
Best regards,
Andy
try to add the word Distinct after Load. You will have
Load Distinct
YOUR_FIELDS....
Could you upload the excel file? Can try from my end n tell you.
Also the Pivot Dimension & Expression you want to build.
Thanks,
Prabhu
Hi Sergey,
Thanks for your reply, I had tried that but it doesn't solve the double count issue.
Best regards,
Andy