filling the missing values from month to previous months

[Navars]

Hello All,

i have a requirement like , i have four  months data in that APRIL month only i have data for a measure , so what i need here is i need to fill the April month data to previous months.

Providing some sample data in the attachments.

Existing data:

Expected data:

Thanks in Advance!!

[durgesh22]

@Navars 

You want to do this in Table chart or in Script editor?

[Navars]

Anything is fine, i need the expected output.

[vchuprina]

Hi,

You can try following:

DATA_TMP:
LOAD DATE_REFRESHED,
     MODEL,
     SKU,
     ORGANIZATION_NAME,
     Measure
FROM
[C:\Users\chuprynav\Downloads\sample data.xlsx]
(ooxml, embedded labels, table is Sheet1);


DATA:
NoConcatenate
LOAD
      DATE_REFRESHED,
     MODEL,
     SKU,
     ORGANIZATION_NAME,
     IF(SKU = Previous(SKU) and ORGANIZATION_NAME = Previous(ORGANIZATION_NAME), Peek(Measure), Measure) AS Measure
Resident DATA_TMP
Order By MODEL ASC, SKU ASC,ORGANIZATION_NAME DESC, DATE_REFRESHED DESC;
DROP Table DATA_TMP; 

Regards,

Vitalii

[vchuprina]

Result:

[Navars]

Hi Vchuprina,

thanks for your reply, it working on the sample data which i have provided.

But in the dashboard its not giving the expected output.

again i am providing the complete data in QVD in the attachment.

i have a USD_MATERIAL_COST filed and COST_TYPE, where COST_TYPE is equals to Frozen Standard Cost Type

in this case i have to calculate the frozen measure and fill the data for missing months.

sum({<COST_TYPE={'Frozen Standard Cost Type'}>}USD_MATERIAL_COST) -- New measure 

if we use above expression i am getting data only for the month of April, then i need to populate same data for the previous months.(what value is there in April, need to populate to Jan, feb, mar) and one more point here these 3 months data should remains same if we move to next month(May).May month data we will get it from DB.

In the table chart im using the below Dimensions. DATE_REFRESHED, SKU, MODEL, ORGANIZATION_NAME, Frozen (Measure)

Thanks in Advance!!

[vchuprina]

Hi, 

I’ve checked your qvd, my solution doesn’t work on your data because you have another type of problem. 
In sample data you show that date field is populated, but values are available only for some of them.

In you qvd you have only date and value when status was changed, so to solve a problem first you should add missing months and populate values for them

Follow this article, you will need just modify solution a bit accordingly to your data source:

https://community.qlik.com/t5/Qlik-Design-Blog/How-to-populate-a-sparsely-populated-field/ba-p/14706...

Regards,

Vitalii