topic Re: Need to populate a pivot table with previous available value where the current value is null or zero in App Development

Need to populate a pivot table with previous available value where the current value is null or zero

QlikNAM — Wed, 26 Jun 2024 10:06:08 GMT

I need to fill the missing months with previous available value as below,in a pivot table in the front end .

my existing formula looks like

sum(if(A_Date=another_Date,NUM(MEASURE1,'0.00;(0.00)'))), i just want to carry forward the previous value until a period has a value >0, Original is how i see my pivot table now Desired is what i expect , any help any tips with set analysis or direct is appreciated

Re: Need to populate a pivot table with previous available value where the current value is null or zero

ali_hijazi — Wed, 26 Jun 2024 11:29:54 GMT

if there is no data or rows for missing months then you need to add them to your fact table with zero as the MEASURE
so first you need to generate months from the min available month till today for example:

let StartDate = num(DayStart(YearStart(MakeDate(LEFT('$(START_MONTH)',4),RIGHT('$(START_MONTH)',2)))));

let EndDate = num(daystart((MakeDate(LEFT('$(END_MONTH)',4),RIGHT('$(END_MONTH)',2)))));

//Create a temporary calendar

TempCalendar:

load

recno() as Date_Key,

'$(StartDate)'+recno()-1 as PERIOD_DATE

autogenerate($(EndDate)-$(StartDate)+1);

load

distinct

MONTHNAME(PERIOD_DATE) as MONTH_YEAR,

num(MONTHNAME(PERIOD_DATE)) as NMY

resident TempCalendar order by PERIOD_DATE Asc;

drop table TempCalendar;

then you cross join the result here with the distinct combination of the dimensions in your fact table
finally concatenate the result to your fact table

Re: Need to populate a pivot table with previous available value where the current value is null or zero

QlikNAM — Wed, 26 Jun 2024 11:41:24 GMT

Hi i understand that , i have the complete calendar with the missing months showing as zero , i don't want to show the zero i want to show the previous months value if the value = 0..

iv created the formula below

if(sum(if(A_Date=another_Date,NUM(MEASURE1,'0.00;(0.00)')))<=0,

before(sum(if(A_Date=another_Date,NUM(MEASURE1,'0.00;(0.00)')))),
sum(if(A_Date=another_Date,NUM(MEASURE1,'0.00;(0.00)')))

this takes the previous value into the next 1 column but not the subsequent columns, i need it to continue copying the same value until it hits a column with a value > 0.

Re: Need to populate a pivot table with previous available value where the current value is null or zero

ali_hijazi — Wed, 26 Jun 2024 14:41:12 GMT

then you need to use the RangeSum instead of Before