Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
Navars
Creator
Creator

filling the missing values from month to previous months

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:

Navars_0-1650963219935.png

Expected data:

Navars_1-1650963314341.png

 

Thanks in Advance!!

 

 

Labels (3)
6 Replies
durgesh22
Creator
Creator

@Navars 

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

Navars
Creator
Creator
Author

Anything is fine, i need the expected output.

vchuprina
Specialist
Specialist

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

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
vchuprina
Specialist
Specialist

Result:

vchuprina_0-1650965331672.png

 

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
Navars
Creator
Creator
Author

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
Specialist
Specialist

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

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").